﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using QuotingTool.Domain.Models;
using QuotingTool.SForce;
using System.Configuration;
using System.Net;
using System.Data;
using Oracle.DataAccess.Client;

namespace QuotingTool.Domain.DataAccess.Repositories
{
    public static class ExtensionMethods
    {
        public static string ValueOrEmpty(this object s)
        {
            return s == null ? "" : s.ToString();
        }


        public static string DateOnlyValueOrEmpty(this object s)
        {
            DateTime dtValue;

            if (s != null)
            {
                if (DateTime.TryParse(s.ToString(), out dtValue))
                {
                    return dtValue.ToString("dd/MM/yyyy");
                }
                else
                {
                    return s.ToString();
                }
            }
            else
            {
                return string.Empty;
            }
        }
    }

    public class OracleRepository
    {
        private string connectionString;

        public OracleRepository(string connectionString)
        {
            this.connectionString = connectionString;
        }


        #region Quotes

        // Get Quote
        public DBQuote GetQuote(string QuoteHeaderID)
        {

            DBQuote Quote = GetQuoteHeaders(QuoteHeaderID);

            // Set line items
            if (!string.IsNullOrEmpty(Quote.Header.HEADER_ID))
            {
                List<DBLineItem> quoteLineItems = GetLineItems(Quote.Header.HEADER_ID);

                if (quoteLineItems != null && quoteLineItems.Count > 0)
                {
                    Quote.Lines = quoteLineItems;
                }
            }
            return Quote;
        }

        public DBQuote GetQuoteSublines(string QuoteNumber, string LineID)
        {

            DBQuote Quote = new DBQuote();

            // Get sublines
            List<DBSubLineItem> quoteSubLineItems = GetSubLineItems(QuoteNumber, LineID);
            Quote.SubLines = quoteSubLineItems;
            return Quote;
        }

        public DBQuote CreateQuote(string ContractsIDs, string UserName, string LeadContract, string MAMaintSource, string MAMaintRate, string MAMaintCol, string Currency)
        {
            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_contract_id = new OracleParameter("p_contract_id", OracleDbType.Varchar2);
            p_contract_id.Value = ContractsIDs;

            OracleParameter p_Lead_Contract_Id = new OracleParameter("p_Lead_Contract_Id", OracleDbType.Varchar2);
            p_Lead_Contract_Id.Value = LeadContract;


            OracleParameter p_MA_Maint_Source = new OracleParameter("p_MA_Maint_Source", OracleDbType.Varchar2);
            p_MA_Maint_Source.Value = MAMaintSource;


            OracleParameter p_MA_Maint_Rate = new OracleParameter("p_MA_Maint_Rate", OracleDbType.Varchar2);
            p_MA_Maint_Rate.Value = MAMaintRate;


            OracleParameter p_MA_Maint_COL = new OracleParameter("p_MA_Maint_COL", OracleDbType.Varchar2);
            p_MA_Maint_COL.Value = MAMaintCol;

            OracleParameter p_Currency_Code = new OracleParameter("p_Currency_Code", OracleDbType.Varchar2);
            p_Currency_Code.Value = Currency;
            

        
            OracleParameter p_user_name = new OracleParameter("p_user_name", OracleDbType.Varchar2);
            p_user_name.Value = UserName;

            OracleParameter p_quote_header_id = new OracleParameter("p_quote_header_id", OracleDbType.Int64);
            p_quote_header_id.Direction = ParameterDirection.Output;

            OracleParameter p_error_msg = new OracleParameter("p_error_msg", OracleDbType.Varchar2);
            p_error_msg.Direction = ParameterDirection.Output;


            oraParams.Add(p_contract_id);
            oraParams.Add(p_Lead_Contract_Id);
            oraParams.Add(p_MA_Maint_Source);
            oraParams.Add(p_MA_Maint_Rate);
            oraParams.Add(p_MA_Maint_COL);
            oraParams.Add(p_Currency_Code);
            oraParams.Add(p_user_name);
            oraParams.Add(p_quote_header_id);
            oraParams.Add(p_error_msg);

            DBQuote newLineItem = ExecuteQueryReturnParamCreateQuote("NICE_OKS_QUOTING_UTILS.initial_quote_data", oraParams);

            return newLineItem;
        }
     
        // Update quote
        public DBQuote UpdateQuote(DBQuote Quote, string UserName)
        {
            if (Quote.SubLines != null && Quote.SubLines.Count > 0)
            {
                List<DBSubLineItem> oUpdatedSubLines = new List<DBSubLineItem>();

                foreach (DBSubLineItem item in Quote.SubLines)
                {
                    DBQuote UpdatedSubLine = UpdateSubLine(item, UserName);

                    oUpdatedSubLines.Add(UpdatedSubLine.SubLines[0]);
                }

                Quote.Header = GetQuoteHeaders(Quote.Header.HEADER_ID).Header;
                Quote.Lines = GetLineItems(Quote.Header.HEADER_ID);
                Quote.SubLines = oUpdatedSubLines;
            }
            else if (Quote.Lines != null && Quote.Lines.Count > 0)
            {
                foreach (DBLineItem item in Quote.Lines)
                {
                    UpdateLineItem(item, UserName);
                }

            }
            else if (Quote.Header.HEADER_ID != null)
            {
                UpdateQuoteHeader(Quote, UserName);
                Quote.Header = GetQuoteHeaders(Quote.Header.HEADER_ID).Header;
            }

            return Quote;
        }

        public DBQuote CopyQuote(string HeaderID, string UserName)
        {

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Quote_Header_Id = new OracleParameter("p_Quote_Header_Id ", OracleDbType.Int64);
            p_Quote_Header_Id.Value = HeaderID;
            oraParams.Add(p_Quote_Header_Id);

            OracleParameter p_User_Name = new OracleParameter("p_User_Name", OracleDbType.Varchar2);
            p_User_Name.Value = UserName;
            oraParams.Add(p_User_Name);

            OracleParameter p_New_Header_Id = new OracleParameter("p_New_Header_Id", OracleDbType.Int64);
            p_New_Header_Id.Direction = ParameterDirection.Output;
            oraParams.Add(p_New_Header_Id);

            OracleParameter p_error = new OracleParameter("p_Error", OracleDbType.Varchar2);
            p_error.Direction = ParameterDirection.Output;
            oraParams.Add(p_error);

            DBQuote newLineItem = ExecuteQueryCopy("NICE_OKS_QUOTING_UTILS.Copy_Quote", oraParams);

            return newLineItem;
        }


        #endregion

        #region General Getters


        // Operating Units
        public List<DBDataItem> GetOperatingUnits()
        {
            List<DBDataItem> lstTopDivisions = new List<DBDataItem>();

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Operating_Units", null, "p_OU_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBDataItem oTopDivision = new DBDataItem();
                    oTopDivision.KEY = row["ORGANIZATION_ID"].ValueOrEmpty();
                    oTopDivision.VALUE = row["NAME"].ValueOrEmpty();
                    lstTopDivisions.Add(oTopDivision);
                }
            }
            return lstTopDivisions;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<DBTopRegion> GetTopRegions()
        {
            List<DBTopRegion> lstTopRegions = new List<DBTopRegion>();

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Top_Regions", null, "p_Top_Regions_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBTopRegion oTopRegion = new DBTopRegion();
                    oTopRegion.TopRegionName = row["TOP_REGION_NAME"].ValueOrEmpty();
                    oTopRegion.TopRegionCode = row["TOP_REGION_CODE"].ValueOrEmpty();

                    lstTopRegions.Add(oTopRegion);
                }
            }
            return lstTopRegions;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="BeginsWith"></param>
        /// <returns></returns>
        public List<People> GetPersons(string BeginsWith)
        {
            List<People> lstPeople = new List<People>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_begins_with = new OracleParameter("p_begins_with ", OracleDbType.Varchar2);
            p_begins_with.Value = BeginsWith;

            oraParams.Add(p_begins_with);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.get_person_name", oraParams, "p_Customer_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    People oPeople = new People();

                    //full_name and user_name

                    oPeople.FullName = row["FULL_NAME"].ValueOrEmpty();
                    oPeople.UserName = row["USER_NAME"].ValueOrEmpty();

                    lstPeople.Add(oPeople);
                }
            }
            return lstPeople;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="HeaderID"></param>
        /// <returns></returns>
        public string SetPrinaryQuote(string HeaderID)
        {

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Quote_Header_Id = new OracleParameter("p_Quote_Header_Id ", OracleDbType.Varchar2);
            p_Quote_Header_Id.Value = HeaderID;

            OracleParameter p_error = new OracleParameter("p_Error", OracleDbType.Varchar2);
            p_error.Direction = ParameterDirection.Output;
            
             

            
            oraParams.Add(p_Quote_Header_Id);
            oraParams.Add(p_error);

            string sError = ExecuteQueryReturnParam("NICE_OKS_QUOTING_UTILS.Set_Primary_Quote", oraParams);

            return sError;
        }


        /// <summary>
        /// TopDivision
        /// </summary>
        /// <returns></returns>
        public List<TopDivision> GetTopDivisions()
        {
            List<TopDivision> lstTopDivisions = new List<TopDivision>();

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Top_Divisions", null, "p_Top_Divisions_Cur ");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    TopDivision oTopDivision = new TopDivision();
                    oTopDivision.TopDivisionID = row["TOP_DIVISION_ID"].ValueOrEmpty();
                    oTopDivision.TopDivisionName = row["TOP_DIVISION_NAME"].ValueOrEmpty();

                    lstTopDivisions.Add(oTopDivision);
                }
            }
            return lstTopDivisions;
        }

        /// <summary>
        /// procedure Get_Currencies (p_Currencies_Cur out sys_refcursor);
        /// </summary>
        /// <returns></returns>
        public List<DBCurrency> GetCurrnecies()
        {
            List<DBCurrency> lstTopDivisions = new List<DBCurrency>();

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Currencies", null, "p_Currencies_Cur ");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBCurrency oTopDivision = new DBCurrency();
                    oTopDivision.NAME = row["NAME"].ValueOrEmpty();
                    oTopDivision.CODE = row["CURRENCY_CODE"].ValueOrEmpty();

                    lstTopDivisions.Add(oTopDivision);
                }
            }
            return lstTopDivisions;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="HeaderId"></param>
        /// <param name="CustomerType"></param>
        /// <returns></returns>
        public List<DBDataItem> GetQuoteCustomers(string HeaderId, string CustomerType)
        {
            List<DBDataItem> lstTopDivisions = new List<DBDataItem>();

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Quote_Header_Id = new OracleParameter("p_Quote_Header_Id ", OracleDbType.Varchar2);
            p_Quote_Header_Id.Value = HeaderId;
            oraParams.Add(p_Quote_Header_Id);

            OracleParameter p_Customer_Type = new OracleParameter("p_Customer_Type ", OracleDbType.Varchar2);
            p_Customer_Type.Value = CustomerType;
            oraParams.Add(p_Customer_Type);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Quote_Customers", oraParams, "p_Customer_Cur ");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBDataItem oTopDivision = new DBDataItem();
                    oTopDivision.KEY = row["PARTY_ID"].ValueOrEmpty();
                    oTopDivision.VALUE = row["CUSTOMER_NAME"].ValueOrEmpty();

                    lstTopDivisions.Add(oTopDivision);
                }
            }
            return lstTopDivisions;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SoldToPartyId"></param>
        /// <param name="OperatingUnitId"></param>
        /// <returns></returns>
        public List<DBDataItem> GetCustomersBySoldTo(string SoldToPartyId, string OperatingUnitId)
        {
            List<DBDataItem> lstTopDivisions = new List<DBDataItem>();

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Sold_To_Party_Id = new OracleParameter("p_Sold_To_Party_Id ", OracleDbType.Varchar2);
            p_Sold_To_Party_Id.Value = SoldToPartyId;
            oraParams.Add(p_Sold_To_Party_Id);

            OracleParameter p_Operating_Unit_Id = new OracleParameter("p_Operating_Unit_Id ", OracleDbType.Varchar2);
            p_Operating_Unit_Id.Value = OperatingUnitId;
            oraParams.Add(p_Operating_Unit_Id);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Customers_By_Sold_To", oraParams, "p_Customers_Cur ");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBDataItem oTopDivision = new DBDataItem();
                    oTopDivision.KEY = row["CUSTOMER_ID"].ValueOrEmpty();
                    oTopDivision.VALUE = row["CUSTOMER_NAME"].ValueOrEmpty();

                    lstTopDivisions.Add(oTopDivision);
                }
            }
            return lstTopDivisions;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="CustAccountId"></param>
        /// <param name="SiteUseType"></param>
        /// <param name="OperatingUnitId"></param>
        /// <returns></returns>
        public List<DBBillShipAddress> GetBillShipAddress(string CustAccountId, string SiteUseType, string OperatingUnitId)
        {
            List<DBBillShipAddress> lstTopDivisions = new List<DBBillShipAddress>();

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Cust_Account_Id = new OracleParameter("p_Cust_Account_Id ", OracleDbType.Varchar2);
            p_Cust_Account_Id.Value = CustAccountId;
            oraParams.Add(p_Cust_Account_Id);

            OracleParameter p_Site_Use_Type = new OracleParameter("p_Site_Use_Type ", OracleDbType.Varchar2);
            p_Site_Use_Type.Value = SiteUseType;
            oraParams.Add(p_Site_Use_Type);

            OracleParameter p_Operating_Unit_Id = new OracleParameter("p_Operating_Unit_Id ", OracleDbType.Varchar2);
            p_Operating_Unit_Id.Value = OperatingUnitId;
            oraParams.Add(p_Operating_Unit_Id);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Bill_Ship_Address", oraParams, "p_Bill_Ship_Address_Cur ");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBBillShipAddress oTopDivision = new DBBillShipAddress();
                    oTopDivision.ADDRESS = row["ADDRESS"].ValueOrEmpty();
                    oTopDivision.SITE_USE_CODE = row["SITE_USE_CODE"].ValueOrEmpty();
                    oTopDivision.SITE_USE_ID = row["SITE_USE_ID"].ValueOrEmpty();
                    lstTopDivisions.Add(oTopDivision);
                }
            }
            return lstTopDivisions;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TopDivision"></param>
        /// <returns></returns>
        public List<DBDataItem> GetMaintenanceItems(string TopDivision)
        {
            List<DBDataItem> lstTopDivisions = new List<DBDataItem>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Top_Division = new OracleParameter("p_Top_Division ", OracleDbType.NVarchar2);
            p_Top_Division.Value = TopDivision;
            oraParams.Add(p_Top_Division);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Maintenance_Items", oraParams, "p_Maint_Items_Cur ");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBDataItem oTopDivision = new DBDataItem();
                    oTopDivision.KEY = row["INVENTORY_ITEM_ID"].ValueOrEmpty();
                    oTopDivision.VALUE = row["MAINTENANCE_ITEM"].ValueOrEmpty();
                    lstTopDivisions.Add(oTopDivision);
                }
            }
            return lstTopDivisions;
        }


        #endregion

        #region Quote Line

        /// <summary>
        /// Insert
        /// </summary>
        /// <param name="LineItem"></param>
        /// <param name="UserName"></param>
        /// <param name="HeaderID"></param>
        /// <returns></returns>
        public DBLineItem InsertLineItem(DBLineItem LineItem, string UserName, string HeaderID)
        {
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_line_id = new OracleParameter("p_Line_Id", OracleDbType.Int64);

            p_line_id.Direction = ParameterDirection.Output;

            OracleParameter p_header_id = new OracleParameter("p_Header_Id", OracleDbType.Int64);
            p_header_id.Value = HeaderID;


            OracleParameter p_inventory_item_id = new OracleParameter("p_Inventory_Item_Id", OracleDbType.Int64);
            if (string.IsNullOrEmpty(LineItem.INVENTORY_ITEM_ID))
            {

                p_inventory_item_id.Value = null;
            }
            else
            {
                p_inventory_item_id.Value = LineItem.INVENTORY_ITEM_ID;
            }


            OracleParameter p_Maintenance_Type = new OracleParameter("p_Maintenance_Type", OracleDbType.Varchar2);
            p_Maintenance_Type.Value = LineItem.MAINTENANCE_TYPE;

            OracleParameter p_Maintenance_Policy = new OracleParameter("p_Maintenance_Policy", OracleDbType.Int64);
            if (string.IsNullOrEmpty(LineItem.MAINTENANCE_POLICY))
            {

                p_Maintenance_Policy.Value = null;
            }
            else
            {
                p_Maintenance_Policy.Value = LineItem.MAINTENANCE_POLICY;
            }


            OracleParameter p_Maintenance_Policy_Source = new OracleParameter("p_Maintenance_Policy_Source", OracleDbType.Varchar2);
            p_Maintenance_Policy_Source.Value = LineItem.MAINTENANCE_POLICY_SOURCE;


            OracleParameter p_Total_Amount = new OracleParameter("p_Total_Amount", OracleDbType.Double);
            p_Total_Amount.Value = double.Parse(LineItem.TOTAL_AMOUNT);

            OracleParameter p_User_Name = new OracleParameter("p_User_Name", OracleDbType.Varchar2);
            p_User_Name.Value = UserName;


            OracleParameter p_error = new OracleParameter("p_Error", OracleDbType.Varchar2);
            p_error.Direction = ParameterDirection.Output;

            oraParams.Add(p_line_id);
            oraParams.Add(p_header_id);
            oraParams.Add(p_inventory_item_id);
            oraParams.Add(p_Maintenance_Type);
            oraParams.Add(p_Maintenance_Policy);
            oraParams.Add(p_Maintenance_Policy_Source);
            oraParams.Add(p_Total_Amount);
            oraParams.Add(p_User_Name);
            oraParams.Add(p_error);

            DBLineItem newLineItem = ExecuteQueryReturnParamInsertUpdateLineItem("NICE_OKS_QUOTING_UTILS.insert_quote_line", oraParams);

            return newLineItem;
        }

        /// <summary>
        /// Update
        /// </summary>
        /// <param name="LineItem"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public DBLineItem UpdateLineItem(DBLineItem LineItem, string UserName)
        {
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_line_id = new OracleParameter("p_Line_Id", OracleDbType.Int64);
            p_line_id.Value = LineItem.LINE_ID;

            OracleParameter p_inventory_item_id = new OracleParameter("p_Inventory_Item_Id", OracleDbType.Int64);
            if (string.IsNullOrEmpty(LineItem.INVENTORY_ITEM_ID))
            {

                p_inventory_item_id.Value = null;
            }
            else
            {
                p_inventory_item_id.Value = LineItem.INVENTORY_ITEM_ID;
            }

            OracleParameter p_Maint_Item_Desc = new OracleParameter("p_Maint_Item_Desc", OracleDbType.Varchar2);
            p_Maint_Item_Desc.Value = LineItem.MAINTENANCE_ITEM_DESCRIPTION;

            OracleParameter p_Coverage_Type = new OracleParameter("p_Coverage_Type", OracleDbType.Varchar2);
            p_Coverage_Type.Value = LineItem.COVERAGE_TYPE;

            OracleParameter p_Col = new OracleParameter("p_Col", OracleDbType.Varchar2);
            p_Col.Value = LineItem.COL;

            OracleParameter p_Total_Amount = new OracleParameter("p_Total_Amount", OracleDbType.Double);
            p_Total_Amount.Value = double.Parse(LineItem.TOTAL_AMOUNT);

            OracleParameter p_User_Name = new OracleParameter("p_User_Name", OracleDbType.Varchar2);
            p_User_Name.Value = UserName;


            OracleParameter p_error = new OracleParameter("p_Error", OracleDbType.Varchar2);
            p_error.Direction = ParameterDirection.Output;

            oraParams.Add(p_line_id);
            oraParams.Add(p_inventory_item_id);
            oraParams.Add(p_Maint_Item_Desc);
            oraParams.Add(p_Coverage_Type);
            oraParams.Add(p_Col);
            oraParams.Add(p_Total_Amount);
            oraParams.Add(p_User_Name);
            oraParams.Add(p_error);

            DBLineItem newLineItem = ExecuteQueryReturnParamInsertUpdateLineItem("NICE_OKS_QUOTING_UTILS.Update_Quote_Line", oraParams);

            return newLineItem;
        }

        /// <summary>
        /// Get
        /// </summary>
        /// <param name="HeaderID"></param>
        /// <returns></returns>
        public List<DBLineItem> GetLineItems(string HeaderID)
        {
            List<DBLineItem> lstLineItems = new List<DBLineItem>();
            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_header_id = new OracleParameter("p_header_id", OracleDbType.Int32);
            p_header_id.Value = HeaderID;

            OracleParameter p_error = new OracleParameter("p_error", OracleDbType.Varchar2, ParameterDirection.Output);
            OracleParameter p = new OracleParameter("p_lines", OracleDbType.RefCursor, ParameterDirection.Output);

            oraParams.Add(p_header_id);
            oraParams.Add(p);
            oraParams.Add(p_error);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.get_quote_lines", oraParams, null);

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBLineItem oDbItem = new DBLineItem();

                    oDbItem.LINE_ID = row["LINE_ID"].ValueOrEmpty();
                    oDbItem.QUOTE_HEADER_ID = row["QUOTE_HEADER_ID"].ValueOrEmpty();
                    oDbItem.INVENTORY_ITEM_ID = row["INVENTORY_ITEM_ID"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_ITEM = row["MAINTENANCE_ITEM"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_ITEM_DESCRIPTION = row["MAINTENANCE_ITEM_DESCRIPTION"].ValueOrEmpty();
                    oDbItem.COVERAGE_TYPE = row["COVERAGE_TYPE"].ValueOrEmpty();

                    oDbItem.COL = row["COL"].ValueOrEmpty();
                    //oDbItem.MAINTENANCE_POLICY_SOURCE = row["MAINTENANCE_POLICY_SOURCE"].ValueOrEmpty();
                    oDbItem.TOTAL_AMOUNT = row["TOTAL_AMOUNT"].ValueOrEmpty();

                    oDbItem.CREATED_BY = row["CREATED_BY"].ValueOrEmpty();
                    oDbItem.CREATION_DATE = row["CREATION_DATE"].ValueOrEmpty();
                    oDbItem.LAST_UPDATED_BY = row["LAST_UPDATED_BY"].ValueOrEmpty();
                    oDbItem.LAST_UPDATE_DATE = row["LAST_UPDATE_DATE"].ValueOrEmpty();
                    oDbItem.LAST_UPDATE_LOGIN = row["LAST_UPDATE_LOGIN"].ValueOrEmpty();

                    oDbItem.START_DATE = row["START_DATE"].ValueOrEmpty();
                    oDbItem.END_DATE = row["END_DATE"].ValueOrEmpty();
                    oDbItem.DURATION = row["DURATION"].ValueOrEmpty();

                    lstLineItems.Add(oDbItem);
                }
            }
            return lstLineItems;
        }

        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="LineID"></param>
        /// <returns></returns>
        public string DeleteQuoteLine(string LineID)
        {
            string sRet = string.Empty;

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_line_id = new OracleParameter("p_line_id", OracleDbType.Int64);
            p_line_id.Direction = ParameterDirection.Input;
            p_line_id.Value = LineID;

            OracleParameter p_error = new OracleParameter("p_error ", OracleDbType.Varchar2, ParameterDirection.Output);

            oraParams.Add(p_line_id);
            oraParams.Add(p_error);

            sRet = ExecuteQueryReturnParam("NICE_OKS_QUOTING_UTILS.delete_quote_line", oraParams);

            return sRet;
        }

        #endregion

        #region Quote Sub Line

        /// <summary>
        /// Update subline
        /// </summary>
        /// <param name="SubLine"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public DBQuote UpdateSubLine(DBSubLineItem SubLine, string UserName)
        {
            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_Maintenance_Item_Id = new OracleParameter("p_Maintenance_Item_Id", OracleDbType.Varchar2);
            OracleParameter p_Subline_Id = new OracleParameter("p_Subline_Id", OracleDbType.Varchar2);
            OracleParameter p_Covered_Product_Id = new OracleParameter("p_Covered_Product_Id", OracleDbType.Varchar2);
            OracleParameter p_solution_version = new OracleParameter("p_solution_version", OracleDbType.Varchar2);
            OracleParameter p_Sunset_Product = new OracleParameter("p_Sunset_Product", OracleDbType.Varchar2);
            OracleParameter p_Instance_Number = new OracleParameter("p_Instance_Number", OracleDbType.Varchar2);
            OracleParameter p_Serial_Number = new OracleParameter("p_Serial_Number", OracleDbType.Varchar2);
            OracleParameter p_Division = new OracleParameter("p_Division", OracleDbType.Varchar2);
            OracleParameter p_Top_Division = new OracleParameter("p_Top_Division", OracleDbType.Varchar2);
            OracleParameter p_Installed_At_Country = new OracleParameter("p_Installed_At_Country", OracleDbType.Varchar2);
            OracleParameter p_Installed_At_City = new OracleParameter("p_Installed_At_City", OracleDbType.Varchar2);
            OracleParameter p_Ship_To_Customer_Id = new OracleParameter("p_Ship_To_Customer_Id", OracleDbType.Varchar2);
            OracleParameter p_Bill_To_Customer_Id = new OracleParameter("p_Bill_To_Customer_Id", OracleDbType.Varchar2);
            OracleParameter p_Quantity = new OracleParameter("p_Quantity", OracleDbType.Varchar2);
            OracleParameter p_Number_Licences = new OracleParameter("p_Number_Licences", OracleDbType.Varchar2);
            OracleParameter p_Previous_Quantity = new OracleParameter("p_Previous_Quantity", OracleDbType.Varchar2);
            OracleParameter p_Previous_Number_Of_Licences = new OracleParameter("p_Previous_Number_Of_Licences", OracleDbType.Varchar2);
            OracleParameter p_Qty_Num_Of_Lic_Changed = new OracleParameter("p_Qty_Num_Of_Lic_Changed", OracleDbType.NVarchar2);
            OracleParameter p_Start_Date = new OracleParameter("p_Start_Date", OracleDbType.Date);
            OracleParameter p_End_Date = new OracleParameter("p_End_Date", OracleDbType.Date);
            OracleParameter p_Sales_Order_Number = new OracleParameter("p_Sales_Order_Number", OracleDbType.Varchar2);
            OracleParameter p_Product_Initial_Po_Number = new OracleParameter("p_Product_Initial_Po_Number", OracleDbType.Varchar2);          
            OracleParameter p_Product_Orig_Ship_Date = new OracleParameter("p_Product_Orig_Ship_Date", OracleDbType.Date);
            OracleParameter p_Last_Renewal_Contract_Po_Num = new OracleParameter("p_Last_Renewal_Contract_Po_Num", OracleDbType.Varchar2);
            OracleParameter p_Maintanence_Type = new OracleParameter("p_Maintanence_Type", OracleDbType.Varchar2);
            OracleParameter p_Subline_Status = new OracleParameter("p_Subline_Status", OracleDbType.Varchar2);
            OracleParameter p_Total_List_Price = new OracleParameter("p_Total_List_Price", OracleDbType.Varchar2);
            OracleParameter p_Total_Net_Price = new OracleParameter("p_Total_Net_Price", OracleDbType.Varchar2);
            OracleParameter p_Previous_Amount = new OracleParameter("p_Previous_Amount", OracleDbType.Varchar2);
            OracleParameter p_Maintanence_Source = new OracleParameter("p_Maintanence_Source", OracleDbType.Varchar2);
            OracleParameter p_Maintenance_Rate = new OracleParameter("p_Maintenance_Rate", OracleDbType.Varchar2);
            OracleParameter p_Base_Maintanence_Price = new OracleParameter("p_Base_Maintanence_Price", OracleDbType.Varchar2);
            OracleParameter p_Current_Amount = new OracleParameter("p_Current_Amount", OracleDbType.Varchar2);
            OracleParameter p_Col = new OracleParameter("p_Col", OracleDbType.Varchar2);
            OracleParameter p_Maint_Price_Including_Col = new OracleParameter("p_Maint_Price_Including_Col", OracleDbType.Varchar2);
            OracleParameter p_Annual_Maint_Price_Incl_Col = new OracleParameter("p_Annual_Maint_Price_Incl_Col", OracleDbType.Varchar2);
            OracleParameter p_Source_Order = new OracleParameter("p_Source_Order", OracleDbType.Varchar2);
            OracleParameter p_Source_Contract = new OracleParameter("p_Source_Contract", OracleDbType.Varchar2);
            OracleParameter p_Back_Bill = new OracleParameter("p_Back_Bill", OracleDbType.Varchar2);
            OracleParameter p_Policy_Maint_Rate = new OracleParameter("p_Policy_Maint_Rate", OracleDbType.Varchar2);
            OracleParameter p_Policy_Source = new OracleParameter("p_Policy_Source", OracleDbType.Varchar2);
            OracleParameter p_Policy_Col = new OracleParameter("p_Policy_Col", OracleDbType.Varchar2);
            OracleParameter p_MA_Maint_Rate = new OracleParameter("p_MA_Maint_Rate", OracleDbType.Varchar2);
            OracleParameter p_MA_Source = new OracleParameter("p_MA_Source", OracleDbType.Varchar2);
            OracleParameter p_MA_Col = new OracleParameter("p_MA_Col", OracleDbType.Varchar2);
            OracleParameter p_User_Name = new OracleParameter("p_User_Name", OracleDbType.Varchar2);
            OracleParameter p_Subline = new OracleParameter("p_Subline ", OracleDbType.RefCursor);
            p_Subline.Direction = ParameterDirection.Output;
            OracleParameter p_Error = new OracleParameter("p_Error", OracleDbType.Varchar2);
            p_Maintenance_Item_Id.Value = SubLine.MAINTENANCE_ITEM_ID;
            p_Subline_Id.Value = SubLine.SUBLINE_ID;
            p_Covered_Product_Id.Value = SubLine.COVERED_PRODUCT_ID;
            p_solution_version.Value = SubLine.SOLUTION_VERSION;
            p_Sunset_Product.Value = SubLine.SUNSET_PRODUCT;
            p_Instance_Number.Value = SubLine.INSTANCE_NUMBER;
            p_Serial_Number.Value = SubLine.SERIAL_NUMBER;
            p_Division.Value = SubLine.DIVISION;
            p_Top_Division.Value = SubLine.TOP_DIVISION;
            p_Installed_At_Country.Value = SubLine.INSTALLED_AT_COUNTRY;
            p_Installed_At_City.Value = SubLine.INSTALLED_AT_CITY;
            p_Ship_To_Customer_Id.Value = SubLine.SHIP_TO_CUSTOMER_ID;
            p_Bill_To_Customer_Id.Value = SubLine.BILL_TO_CUSTOMER_ID;
            p_Quantity.Value = SubLine.QUANTITY;
            p_Number_Licences.Value = SubLine.NUMBER_LICENCES;
            p_Previous_Quantity.Value = SubLine.PREVIOUS_QUANTITY;
            p_Previous_Number_Of_Licences.Value = SubLine.PREVIOUS_NUMBER_OF_LICENCES;
            p_Qty_Num_Of_Lic_Changed.Value = SubLine.QTY_NUM_OF_LIC_CHANGED;

            //dates can arrive as a string or as a milliseconds long number. 
            //try to parse first from string forma, then from numeric
            DateTime dtStartDate;
            long unixDate;

            bool sdParsedFlag = Int64.TryParse(SubLine.START_DATE, out unixDate);
            if (sdParsedFlag)
            {
                DateTime start = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
                dtStartDate = start.AddMilliseconds(unixDate).ToLocalTime();  
            }
            else
            {
                DateTime.TryParse(SubLine.START_DATE, out dtStartDate);
            }                       
            p_Start_Date.Value = dtStartDate;


            DateTime dtEND_DATE;
            bool edParsedFlag = Int64.TryParse(SubLine.END_DATE, out unixDate);
            if (edParsedFlag)
            {
                DateTime start = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
                dtEND_DATE = start.AddMilliseconds(unixDate).ToLocalTime();
            }
            else
            {
                DateTime.TryParse(SubLine.END_DATE, out dtEND_DATE);
            }
            p_End_Date.Value = dtEND_DATE;

            //DateTime dtEND_DATE;
            //DateTime.TryParse(SubLine.END_DATE, out dtEND_DATE);
            //if (dtEND_DATE.Year == 1901)          //try to parse from numeric format 
            //{
            //    long unixDate = Int64.Parse(SubLine.END_DATE);
            //    DateTime start = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
            //    dtEND_DATE = start.AddMilliseconds(unixDate).ToLocalTime();                
            //}
            //p_End_Date.Value = dtEND_DATE;          
                       

            p_Sales_Order_Number.Value = SubLine.SALES_ORDER_NUMBER;
            p_Product_Initial_Po_Number.Value = SubLine.PRODUCT_INITIAL_PO_NUMBER;
           
            DateTime dtp_Product_Orig_Ship_Date;
            DateTime.TryParse(SubLine.PRODUCT_ORIG_SHIP_DATE, out dtp_Product_Orig_Ship_Date);
            p_Product_Orig_Ship_Date.Value = dtp_Product_Orig_Ship_Date;

            p_Last_Renewal_Contract_Po_Num.Value = SubLine.SOURCE_CONTRACT_PO_NUM;
            p_Subline_Status.Value = SubLine.SUBLINE_STATUS;
            p_Total_List_Price.Value = SubLine.TOTAL_LIST_PRICE;
            p_Total_Net_Price.Value = SubLine.TOTAL_NET_PRICE;
            p_Previous_Amount.Value = SubLine.PREVIOUS_AMOUNT;
            p_Maintanence_Source.Value = SubLine.MA_SOURCE;
            p_Maintenance_Rate.Value = SubLine.MAINTENANCE_RATE;
            p_Maintanence_Type.Value = SubLine.MAINTANENCE_TPYE;         
            p_Base_Maintanence_Price.Value = SubLine.BASE_MAINTENANCE_PRICE;
            p_Current_Amount.Value = SubLine.CURRENT_AMOUNT;
            p_Col.Value = SubLine.COL;
            p_Maint_Price_Including_Col.Value = SubLine.MAINT_PRICE_INCLUDING_COL;
            p_Annual_Maint_Price_Incl_Col.Value = SubLine.ANNUAL_MAINT_PRICE_INCL_COL;
            p_Source_Order.Value = SubLine.SOURCE_ORDER;
            p_Source_Contract.Value = SubLine.SOURCE_CONTRACT;
            p_Back_Bill.Value = SubLine.BACK_BILL;
            p_Policy_Maint_Rate.Value = SubLine.POLICY_MAINT_RATE;
            p_Policy_Source.Value = SubLine.POLICY_SOURCE;
            p_Policy_Col.Value = SubLine.POLICY_COL;
            p_MA_Maint_Rate.Value = SubLine.MA_MAINT_RATE;
            p_MA_Source.Value = SubLine.MA_SOURCE;
            p_MA_Col.Value = SubLine.MA_COL;
            p_User_Name.Value = UserName;
            p_Error.Direction = ParameterDirection.Output;

            oraParams.Add(p_Subline_Id);
            oraParams.Add(p_Maintenance_Item_Id);
            oraParams.Add(p_Covered_Product_Id);
            oraParams.Add(p_solution_version);
            oraParams.Add(p_Sunset_Product);
            oraParams.Add(p_Instance_Number);
            oraParams.Add(p_Serial_Number);
            oraParams.Add(p_Division);
         //   oraParams.Add(p_Top_Division);
            oraParams.Add(p_Installed_At_Country);
            oraParams.Add(p_Installed_At_City);
            oraParams.Add(p_Ship_To_Customer_Id);
            oraParams.Add(p_Bill_To_Customer_Id);
            oraParams.Add(p_Quantity);
            oraParams.Add(p_Number_Licences);
            oraParams.Add(p_Previous_Quantity);
            oraParams.Add(p_Previous_Number_Of_Licences);
            oraParams.Add(p_Qty_Num_Of_Lic_Changed);
            oraParams.Add(p_Start_Date);
            oraParams.Add(p_End_Date);        
            oraParams.Add(p_Sales_Order_Number);
            oraParams.Add(p_Product_Initial_Po_Number);
        //    oraParams.Add(p_Last_Renewal_Contract_Po_Num);
            oraParams.Add(p_Product_Orig_Ship_Date);           
            oraParams.Add(p_Subline_Status);
            oraParams.Add(p_Total_List_Price);
            oraParams.Add(p_Total_Net_Price);
            oraParams.Add(p_Previous_Amount);
            oraParams.Add(p_Maintanence_Type);
            oraParams.Add(p_Maintanence_Source);
            oraParams.Add(p_Maintenance_Rate);
            oraParams.Add(p_Base_Maintanence_Price);
            oraParams.Add(p_Current_Amount);
            oraParams.Add(p_Col);
            oraParams.Add(p_Maint_Price_Including_Col);
            oraParams.Add(p_Annual_Maint_Price_Incl_Col);
            oraParams.Add(p_Source_Order);
            oraParams.Add(p_Source_Contract);
            oraParams.Add(p_Back_Bill);
            oraParams.Add(p_Policy_Maint_Rate);
            oraParams.Add(p_Policy_Source);
            oraParams.Add(p_Policy_Col);
            oraParams.Add(p_MA_Maint_Rate);
            oraParams.Add(p_MA_Source);
            oraParams.Add(p_MA_Col);
            oraParams.Add(p_User_Name);
            oraParams.Add(p_Subline);
            oraParams.Add(p_Error);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Update_Quote_Subline", oraParams,null);
            List<DBSubLineItem> lstLineItems = new List<DBSubLineItem>();
            DBQuote newItem = new DBQuote();

             if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBSubLineItem oDbItem = new DBSubLineItem();

                    oDbItem.SUBLINE_ID = row["SUBLINE_ID"].ValueOrEmpty();
                    oDbItem.LINE_ID = row["LINE_ID"].ValueOrEmpty();
                    oDbItem.QUOTE_HEADER_ID = row["QUOTE_HEADER_ID"].ValueOrEmpty();
                    oDbItem.SOURCE_SUBLINE_ID = row["SOURCE_SUBLINE_ID"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_ITEM = row["MAINTENANCE_ITEM"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_ITEM_ID = row["MAINTENANCE_ITEM_ID"].ValueOrEmpty();
                    oDbItem.COVERED_PRODUCT_ID = row["COVERED_PRODUCT_ID"].ValueOrEmpty();
                    oDbItem.COVERED_PRODUCT = row["COVERED_PRODUCT"].ValueOrEmpty();
                    oDbItem.COVERED_PRODUCT_DESCRIPTION = row["COVERED_PRODUCT_DESCRIPTION"].ValueOrEmpty();
                    oDbItem.SOLUTION_VERSION = row["SOLUTION_VERSION"].ValueOrEmpty();
                    oDbItem.SUNSET_PRODUCT = row["SUNSET_PRODUCT"].ValueOrEmpty();
                    oDbItem.INSTANCE_NUMBER = row["INSTANCE_NUMBER"].ValueOrEmpty();
                    oDbItem.SERIAL_NUMBER = row["SERIAL_NUMBER"].ValueOrEmpty();
                    oDbItem.DIVISION = row["DIVISION"].ValueOrEmpty();
                    oDbItem.DIVISION_NAME = row["DIVISION_NAME"].ValueOrEmpty();
                    oDbItem.INSTALLED_AT_COUNTRY = row["INSTALLED_AT_COUNTRY"].ValueOrEmpty();
                    oDbItem.INSTALLED_AT_CITY = row["INSTALLED_AT_CITY"].ValueOrEmpty();
                    oDbItem.SHIP_TO_CUSTOMER_ID = row["SHIP_TO_CUSTOMER_ID"].ValueOrEmpty();
                    oDbItem.SHIP_TO_CUSTOMER_NAME = row["SHIP_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oDbItem.BILL_TO_CUSTOMER_ID = row["BILL_TO_CUSTOMER_ID"].ValueOrEmpty();
                    oDbItem.BILL_TO_CUSTOMER_NAME = row["BILL_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oDbItem.QUANTITY = row["QUANTITY"].ValueOrEmpty();
                    oDbItem.NUMBER_LICENCES = row["NUMBER_LICENCES"].ValueOrEmpty();
                    oDbItem.PREVIOUS_QUANTITY = row["PREVIOUS_QUANTITY"].ValueOrEmpty();
                    oDbItem.PREVIOUS_NUMBER_OF_LICENCES = row["PREVIOUS_NUMBER_OF_LICENCES"].ValueOrEmpty();
                    oDbItem.QTY_NUM_OF_LIC_CHANGED = row["QTY_NUM_OF_LIC_CHANGED"].ValueOrEmpty();
                    oDbItem.START_DATE = row["START_DATE"].ValueOrEmpty();
                    oDbItem.END_DATE = row["END_DATE"].ValueOrEmpty();
                  //  oDbItem.DURATION = row["DURATION"].ValueOrEmpty();
                    oDbItem.SALES_ORDER_NUMBER = row["SALES_ORDER_NUMBER"].ValueOrEmpty();
                    oDbItem.PRODUCT_INITIAL_PO_NUMBER = row["PRODUCT_INITIAL_PO_NUMBER"].ValueOrEmpty();
                    oDbItem.SOURCE_CONTRACT_PO_NUM = row["SOURCE_CONTRACT_PO_NUM"].ValueOrEmpty();
                    oDbItem.PRODUCT_ORIG_SHIP_DATE = row["PRODUCT_ORIG_SHIP_DATE"].ValueOrEmpty();
                  //  oDbItem.CONTRACT_IDENTIFIER = row["CONTRACT_IDENTIFIER"].ValueOrEmpty();
                    oDbItem.SUBLINE_STATUS = row["SUBLINE_STATUS"].ValueOrEmpty();
                    oDbItem.TOTAL_LIST_PRICE = row["TOTAL_LIST_PRICE"].ValueOrEmpty();
                    oDbItem.TOTAL_NET_PRICE = row["TOTAL_NET_PRICE"].ValueOrEmpty();
                    oDbItem.PREVIOUS_AMOUNT = row["PREVIOUS_AMOUNT"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_RATE = row["MAINTENANCE_RATE"].ValueOrEmpty();
                    oDbItem.BASE_MAINTENANCE_PRICE = row["BASE_MAINTENANCE_PRICE"].ValueOrEmpty();
                    oDbItem.CURRENT_AMOUNT = row["CURRENT_AMOUNT"].ValueOrEmpty();
                    oDbItem.COL = row["COL"].ValueOrEmpty();
                    oDbItem.MAINT_PRICE_INCLUDING_COL = row["MAINT_PRICE_INCLUDING_COL"].ValueOrEmpty();
                    oDbItem.ANNUAL_MAINT_PRICE_INCL_COL = row["ANNUAL_MAINT_PRICE_INCL_COL"].ValueOrEmpty();
                    oDbItem.DURATION = row["DURATION"].ValueOrEmpty();
                    oDbItem.SOURCE_ORDER = row["SOURCE_ORDER"].ValueOrEmpty();
               //     oDbItem.SOURCE_CONTRACT = row["SOURCE_CONTRACT"].ValueOrEmpty();
                    oDbItem.BACK_BILL = row["BACK_BILL"].ValueOrEmpty();
                    oDbItem.POLICY_MAINT_RATE = row["POLICY_MAINT_RATE"].ValueOrEmpty();
                    oDbItem.POLICY_SOURCE = row["POLICY_SOURCE"].ValueOrEmpty();
                    oDbItem.POLICY_COL = row["POLICY_COL"].ValueOrEmpty();
                    oDbItem.MA_MAINT_RATE = row["MA_MAINT_RATE"].ValueOrEmpty();
                    oDbItem.MA_SOURCE = row["MA_SOURCE"].ValueOrEmpty();
                    oDbItem.MA_COL = row["MA_COL"].ValueOrEmpty();
                    oDbItem.CREATED_BY = row["CREATED_BY"].ValueOrEmpty();
                    oDbItem.CREATION_DATE = row["CREATION_DATE"].ValueOrEmpty();
                    oDbItem.LAST_UPDATED_BY = row["LAST_UPDATED_BY"].ValueOrEmpty();
                    oDbItem.LAST_UPDATE_DATE = row["LAST_UPDATE_DATE"].ValueOrEmpty();
                    oDbItem.LAST_UPDATE_LOGIN = row["LAST_UPDATE_LOGIN"].ValueOrEmpty();
                   
                    lstLineItems.Add(oDbItem);
                }
            }
             newItem.SubLines = lstLineItems;

            return newItem;
        }


        public List<SFDivisionLine> Get_Quote_Amts_By_Division(string HeaderID)
        {
            List<SFDivisionLine> lstLineItems = new List<SFDivisionLine>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Quote_Header_Id = new OracleParameter("p_Quote_Header_Id", OracleDbType.NVarchar2);
            p_Quote_Header_Id.Value = HeaderID;

            OracleParameter p_Sub_Lines = new OracleParameter("p_Quote_Amounts", OracleDbType.RefCursor, ParameterDirection.Output);
            OracleParameter p_Error = new OracleParameter("p_Error", OracleDbType.Varchar2, ParameterDirection.Output);

            oraParams.Add(p_Quote_Header_Id);
            oraParams.Add(p_Sub_Lines);
            oraParams.Add(p_Error);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Quote_Amts_By_Division", oraParams, null);

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    SFDivisionLine oLine = new SFDivisionLine();
                    
                    oLine.DIVISION = row["DIVISION_NAME"].ValueOrEmpty();
                    oLine.PRICE_INCLUDING_COL = row["PRICE_INCLUDING_COL"].ValueOrEmpty();
                    oLine.ANNUAL_PRICE_INCL_COL = row["ANNUAL_PRICE_INCL_COL"].ValueOrEmpty();

                    lstLineItems.Add(oLine);
                }
            }
            return lstLineItems;


        }
       /// <summary>
        /// Get
       /// </summary>
       /// <param name="HeaderID"></param>
       /// <param name="LineID"></param>
       /// <returns></returns>
        public List<DBSubLineItem> GetSubLineItems(string HeaderID, string LineID)
        {
            List<DBSubLineItem> lstLineItems = new List<DBSubLineItem>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_header_id = new OracleParameter("p_quote_header_id", OracleDbType.Int64);
            p_header_id.Value = HeaderID;

            OracleParameter p_Line_Id = new OracleParameter("p_Line_Id", OracleDbType.Int64);
            p_Line_Id.Value = LineID;

            OracleParameter p_Sub_Lines = new OracleParameter("p_sub_lines", OracleDbType.RefCursor, ParameterDirection.Output);
            OracleParameter p_error = new OracleParameter("p_error", OracleDbType.Varchar2, ParameterDirection.Output);

            oraParams.Add(p_header_id);
            oraParams.Add(p_Line_Id);
            oraParams.Add(p_Sub_Lines);
            oraParams.Add(p_error);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.get_quote_sublines", oraParams, null);

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBSubLineItem oDbItem = new DBSubLineItem();

                    oDbItem.SUBLINE_ID = row["SUBLINE_ID"].ValueOrEmpty();
                    oDbItem.LINE_ID = row["LINE_ID"].ValueOrEmpty();
                    oDbItem.QUOTE_HEADER_ID = row["QUOTE_HEADER_ID"].ValueOrEmpty();
                    oDbItem.SOURCE_SUBLINE_ID = row["SOURCE_SUBLINE_ID"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_ITEM = row["MAINTENANCE_ITEM"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_ITEM_ID = row["MAINTENANCE_ITEM_ID"].ValueOrEmpty();                  
                    oDbItem.COVERED_PRODUCT_ID = row["COVERED_PRODUCT_ID"].ValueOrEmpty();
                    oDbItem.COVERED_PRODUCT = row["COVERED_PRODUCT"].ValueOrEmpty();
                    oDbItem.COVERED_PRODUCT_DESCRIPTION = row["COVERED_PRODUCT_DESCRIPTION"].ValueOrEmpty();
                    oDbItem.SOLUTION_VERSION = row["SOLUTION_VERSION"].ValueOrEmpty();
                    oDbItem.SUNSET_PRODUCT = row["SUNSET_PRODUCT"].ValueOrEmpty();
                    oDbItem.INSTANCE_NUMBER = row["INSTANCE_NUMBER"].ValueOrEmpty();
                    oDbItem.SERIAL_NUMBER = row["SERIAL_NUMBER"].ValueOrEmpty();
                    oDbItem.DIVISION = row["DIVISION"].ValueOrEmpty();
                    oDbItem.DIVISION_NAME = row["DIVISION_NAME"].ValueOrEmpty();
                    oDbItem.INSTALLED_AT_COUNTRY = row["INSTALLED_AT_COUNTRY"].ValueOrEmpty();
                    oDbItem.INSTALLED_AT_CITY = row["INSTALLED_AT_CITY"].ValueOrEmpty();
                    oDbItem.SHIP_TO_CUSTOMER_ID = row["SHIP_TO_CUSTOMER_ID"].ValueOrEmpty();
                    oDbItem.SHIP_TO_CUSTOMER_NAME = row["SHIP_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oDbItem.BILL_TO_CUSTOMER_ID = row["BILL_TO_CUSTOMER_ID"].ValueOrEmpty();
                    oDbItem.BILL_TO_CUSTOMER_NAME = row["BILL_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oDbItem.QUANTITY = row["QUANTITY"].ValueOrEmpty();
                    oDbItem.NUMBER_LICENCES = row["NUMBER_LICENCES"].ValueOrEmpty();
                    oDbItem.PREVIOUS_QUANTITY = row["PREVIOUS_QUANTITY"].ValueOrEmpty();
                    oDbItem.PREVIOUS_NUMBER_OF_LICENCES = row["PREVIOUS_NUMBER_OF_LICENCES"].ValueOrEmpty();
                    oDbItem.QTY_NUM_OF_LIC_CHANGED = row["QTY_NUM_OF_LIC_CHANGED"].ValueOrEmpty();
                    oDbItem.START_DATE = row["START_DATE"].ValueOrEmpty();
                    oDbItem.END_DATE = row["END_DATE"].ValueOrEmpty();
                  //  oDbItem.DURATION = row["DURATION"].ValueOrEmpty();
                    oDbItem.SALES_ORDER_NUMBER = row["SALES_ORDER_NUMBER"].ValueOrEmpty();
                    oDbItem.PRODUCT_INITIAL_PO_NUMBER = row["PRODUCT_INITIAL_PO_NUMBER"].ValueOrEmpty();
                    oDbItem.SOURCE_CONTRACT_PO_NUM = row["SOURCE_CONTRACT_PO_NUM"].ValueOrEmpty();
                    oDbItem.PRODUCT_ORIG_SHIP_DATE = row["PRODUCT_ORIG_SHIP_DATE"].ValueOrEmpty();
                  //  oDbItem.CONTRACT_IDENTIFIER = row["CONTRACT_IDENTIFIER"].ValueOrEmpty();
                    oDbItem.SUBLINE_STATUS = row["SUBLINE_STATUS"].ValueOrEmpty();
                    oDbItem.TOTAL_LIST_PRICE = row["TOTAL_LIST_PRICE"].ValueOrEmpty();
                    oDbItem.TOTAL_NET_PRICE = row["TOTAL_NET_PRICE"].ValueOrEmpty();
                    oDbItem.PREVIOUS_AMOUNT = row["PREVIOUS_AMOUNT"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_RATE = row["MAINTENANCE_RATE"].ValueOrEmpty();
                    oDbItem.BASE_MAINTENANCE_PRICE = row["BASE_MAINTENANCE_PRICE"].ValueOrEmpty();
                    oDbItem.CURRENT_AMOUNT = row["CURRENT_AMOUNT"].ValueOrEmpty();
                    oDbItem.COL = row["COL"].ValueOrEmpty();
                    oDbItem.MAINT_PRICE_INCLUDING_COL = row["MAINT_PRICE_INCLUDING_COL"].ValueOrEmpty();
                    oDbItem.ANNUAL_MAINT_PRICE_INCL_COL = row["ANNUAL_MAINT_PRICE_INCL_COL"].ValueOrEmpty();
                    oDbItem.SOURCE_ORDER = row["SOURCE_ORDER"].ValueOrEmpty();
                    oDbItem.SOURCE_CONTRACT_ID = row["SOURCE_CONTRACT_ID"].ValueOrEmpty();
                    oDbItem.SOURCE_CONTRACT_NUMBER = row["SOURCE_CONTRACT_NUMBER"].ValueOrEmpty();
                    oDbItem.BACK_BILL = row["BACK_BILL"].ValueOrEmpty();
                    oDbItem.POLICY_MAINT_RATE = row["POLICY_MAINT_RATE"].ValueOrEmpty();
                    oDbItem.POLICY_SOURCE = row["POLICY_SOURCE"].ValueOrEmpty();
                    oDbItem.POLICY_COL = row["POLICY_COL"].ValueOrEmpty();
                    oDbItem.MA_MAINT_RATE = row["MA_MAINT_RATE"].ValueOrEmpty();
                    oDbItem.MA_SOURCE = row["MA_SOURCE"].ValueOrEmpty();
                    oDbItem.MA_COL = row["MA_COL"].ValueOrEmpty();
                    oDbItem.CREATED_BY = row["CREATED_BY"].ValueOrEmpty();
                    oDbItem.CREATION_DATE = row["CREATION_DATE"].ValueOrEmpty();
                    oDbItem.LAST_UPDATED_BY = row["LAST_UPDATED_BY"].ValueOrEmpty();
                    oDbItem.LAST_UPDATE_DATE = row["LAST_UPDATE_DATE"].ValueOrEmpty();
                    oDbItem.LAST_UPDATE_LOGIN = row["LAST_UPDATE_LOGIN"].ValueOrEmpty();
                    oDbItem.MAINTENANCE_SOURCE = row["MAINTENANCE_SOURCE"].ValueOrEmpty();
                    oDbItem.DURATION = row["DURATION"].ValueOrEmpty();
                    oDbItem.TOP_DIVISION = row["TOP_DIVISION"].ValueOrEmpty();
                    oDbItem.TOP_DIVISION_NAME = row["TOP_DIVISION_NAME"].ValueOrEmpty();
                    lstLineItems.Add(oDbItem);
                }
            }
            return lstLineItems;
        }

       /// <summary>
        /// Delete
       /// </summary>
       /// <param name="SubLineID"></param>
       /// <returns></returns>
        public string DeleteQuoteSubLine(string quotenumber , string SubLineID)
        {
            string sRet = string.Empty;

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_sub_line_id = new OracleParameter("p_sub_lines", OracleDbType.Int64);
            p_sub_line_id.Value = SubLineID;

            OracleParameter p_error = new OracleParameter("p_error", OracleDbType.Varchar2);
            p_error.Direction = ParameterDirection.Output;

            oraParams.Add(p_sub_line_id);
            oraParams.Add(p_error);

            sRet = ExecuteQueryReturnParam("NICE_OKS_QUOTING_UTILS.Delete_Quote_Subline", oraParams);


            return sRet;
        }

        #endregion

        #region Quote Header

        /// <summary>
        /// Update
        /// </summary>
        /// <param name="Quote"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public DBQuote UpdateQuoteHeader(DBQuote Quote, string UserName)
        {
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_header_id = new OracleParameter("p_header_id", OracleDbType.Int64);
            p_header_id.Value = Quote.Header.HEADER_ID;

            OracleParameter p_quote_date = new OracleParameter("p_Quote_Date", OracleDbType.Date);
            DateTime dtQuoteDate;
            DateTime.TryParse(Quote.Header.DATE_APPROVED, out dtQuoteDate);
            p_quote_date.Value = dtQuoteDate;

            OracleParameter p_Installed_At_Party_Id = new OracleParameter("p_Installed_At_Party_Id", OracleDbType.Varchar2);
            p_Installed_At_Party_Id.Value = Quote.Header.INSTALLED_AT_PARTY_ID;

            OracleParameter p_Sold_To_Party_Id = new OracleParameter("p_Sold_To_Party_Id", OracleDbType.Varchar2);
            p_Sold_To_Party_Id.Value = Quote.Header.SOLD_TO_PARTY_ID;

            OracleParameter p_Bill_To_Site_Use_Id = new OracleParameter("p_Bill_To_Site_Use_Id", OracleDbType.Varchar2);
            p_Bill_To_Site_Use_Id.Value = Quote.Header.BILL_TO_SITE_USE_ID;

            OracleParameter p_Ship_To_Site_Use_Id = new OracleParameter("p_Ship_To_Site_Use_Id", OracleDbType.Varchar2);
            p_Ship_To_Site_Use_Id.Value = Quote.Header.SHIP_TO_SITE_USE_ID;

            ////

            OracleParameter p_quote_status = new OracleParameter("p_Quote_Status", OracleDbType.Varchar2);
            p_quote_status.Value = Quote.Header.QUOTE_STATUS;

            OracleParameter p_Opportunity_Number = new OracleParameter("p_Opportunity_Number", OracleDbType.Varchar2);
            p_Opportunity_Number.Value = Quote.Header.OPPORTUNITY_NUMBER;

            OracleParameter p_Sfdc_Account_Name = new OracleParameter("p_Sfdc_Account_Name", OracleDbType.Varchar2);
            p_Sfdc_Account_Name.Value = Quote.Header.SFDC_ACCOUNT_NAME;

            OracleParameter p_Sfdc_Account_Number = new OracleParameter("p_Sfdc_Account_Number", OracleDbType.Varchar2);
            p_Sfdc_Account_Number.Value = Quote.Header.SFDC_ACCOUNT_NUMBER;

            OracleParameter p_Primary_Quote_Flag = new OracleParameter("p_Primary_Quote_Flag", OracleDbType.Varchar2);
            p_Primary_Quote_Flag.Value = Quote.Header.PRIMARY_QUOTE_FLAG;

            OracleParameter p_auto_invoicing_flag = new OracleParameter("p_Auto_invoicing_Flag", OracleDbType.Varchar2);
            p_auto_invoicing_flag.Value = Quote.Header.AUTO_INVOICING_FLAG;

            OracleParameter p_Currency_Code = new OracleParameter("p_Currency_Code", OracleDbType.Varchar2);
            p_Currency_Code.Value = Quote.Header.CURRENCY_CODE;

            OracleParameter p_operating_unit_id = new OracleParameter("p_Operating_Unit_Id", OracleDbType.Varchar2);
            p_operating_unit_id.Value = Quote.Header.OPERATING_UNIT_ID;

            DateTime dtStartDate;
            DateTime.TryParse(Quote.Header.START_DATE, out dtStartDate);
            OracleParameter p_start_date = new OracleParameter("p_Start_Date", OracleDbType.Date);
            p_start_date.Value = dtStartDate;

            DateTime dtEndDate;
            DateTime.TryParse(Quote.Header.END_DATE, out dtEndDate);
            OracleParameter p_end_date = new OracleParameter("p_End_Date", OracleDbType.Date);
            p_end_date.Value = dtEndDate;

            OracleParameter p_duration_uom = new OracleParameter("p_Duration_UOM", OracleDbType.Varchar2);
            p_duration_uom.Value = Quote.Header.DURATION_UOM;

            OracleParameter p_duration = new OracleParameter("p_Duration", OracleDbType.Varchar2);
            p_duration.Value = Quote.Header.DURATION;

            OracleParameter p_total_amount = new OracleParameter("p_Total_Amount", OracleDbType.Varchar2);
            p_total_amount.Value = Quote.Header.TOTAL_AMOUNT;

            OracleParameter p_acv = new OracleParameter("p_ACV", OracleDbType.Varchar2);
            p_acv.Value = Quote.Header.ACV;

            OracleParameter p_Notes_To_Approvers = new OracleParameter("p_Notes_To_Approvers", OracleDbType.Varchar2);
            p_Notes_To_Approvers.Value = Quote.Header.NOTES_TO_APPROVERS;

            OracleParameter p_Cust_PO_Number = new OracleParameter("p_Cust_PO_Number", OracleDbType.Varchar2);
            p_Cust_PO_Number.Value = Quote.Header.CUST_PO_NUMBER;

            //OracleParameter p_One_Line_Discount = new OracleParameter("p_One_Line_Discount", OracleDbType.Varchar2);
            //p_One_Line_Discount.Value = Quote.Header.ONE_LINE_DISCOUNT;

            //OracleParameter p_One_Line_Discount_Amount = new OracleParameter("p_One_Line_Discount_Amount", OracleDbType.Varchar2);
            //p_One_Line_Discount_Amount.Value = Quote.Header.ONE_LINE_DISCOUNT_TYPE;

            //OracleParameter p_Pre_Paid_Indication = new OracleParameter("p_Pre_Paid_Indication", OracleDbType.Varchar2);
            //p_Pre_Paid_Indication.Value = Quote.Header.PRE_PAID_INDICATION;

            //OracleParameter MULTI_YEAR_INCENTIVE = new OracleParameter("p_Multi_Year_Incentive ", OracleDbType.Varchar2);
            //MULTI_YEAR_INCENTIVE.Value = Quote.Header.MULTI_YEAR_INCENTIVE;

            OracleParameter p_Contract_Number_Modifier = new OracleParameter("p_Contract_Number_Modifier", OracleDbType.Varchar2);
            p_Contract_Number_Modifier.Value = Quote.Header.CONTRACT_NUMBER_MODIFIER;

            OracleParameter p_Quote_Owner = new OracleParameter("p_Quote_Owner", OracleDbType.Varchar2);
            /////////////will change when we have real QUOTE_OWNER///////////////////////////////////////
            p_Quote_Owner.Value = Quote.Header.QUOTE_OWNER != null ? Quote.Header.QUOTE_OWNER : "";

            OracleParameter p_User_Name = new OracleParameter("p_User_Name", OracleDbType.Varchar2);
            p_User_Name.Value = UserName;

            OracleParameter p_Bill_To_Customer_Id = new OracleParameter("p_Bill_To_Customer_Id", OracleDbType.Varchar2);
            p_Bill_To_Customer_Id.Value = Quote.Header.BILL_TO_CUSTOMER_ID;

            OracleParameter p_Ship_To_Customer_Id = new OracleParameter("p_Ship_To_Customer_Id", OracleDbType.Varchar2);
            p_Ship_To_Customer_Id.Value = Quote.Header.SHIP_TO_CUSTOMER_ID;

            OracleParameter p_error = new OracleParameter("p_error", OracleDbType.Varchar2);
            p_error.Direction = ParameterDirection.Output;

            oraParams.Add(p_header_id);
            //oraParams.Add(p_quote_date);
            oraParams.Add(p_Installed_At_Party_Id);
            oraParams.Add(p_Sold_To_Party_Id);
            oraParams.Add(p_Bill_To_Customer_Id);
            oraParams.Add(p_Bill_To_Site_Use_Id);
            oraParams.Add(p_Ship_To_Customer_Id);
            oraParams.Add(p_Ship_To_Site_Use_Id);
            oraParams.Add(p_quote_status);
            oraParams.Add(p_Opportunity_Number);
            oraParams.Add(p_Sfdc_Account_Name);
            oraParams.Add(p_Sfdc_Account_Number);
            oraParams.Add(p_Primary_Quote_Flag);
            oraParams.Add(p_auto_invoicing_flag);
            oraParams.Add(p_Currency_Code);
            oraParams.Add(p_operating_unit_id);
            oraParams.Add(p_start_date);
            oraParams.Add(p_end_date);
            oraParams.Add(p_duration_uom);
            oraParams.Add(p_duration);
            oraParams.Add(p_total_amount);
            oraParams.Add(p_acv);
            oraParams.Add(p_Notes_To_Approvers);
            oraParams.Add(p_Cust_PO_Number);
            //oraParams.Add(p_One_Line_Discount);
            //oraParams.Add(p_One_Line_Discount_Amount);
            //oraParams.Add(p_Pre_Paid_Indication);
            //oraParams.Add(MULTI_YEAR_INCENTIVE);
            oraParams.Add(p_Contract_Number_Modifier);
            oraParams.Add(p_Quote_Owner);
            oraParams.Add(p_User_Name);
            oraParams.Add(p_error);

            DBQuote quote = ExecuteQueryReturnParamUpdateQueryHeader("NICE_OKS_QUOTING_UTILS.Update_Quote_Header", oraParams);

            return quote;
        }

       /// <summary>
        /// Get
       /// </summary>
       /// <param name="QuoteHeaderID"></param>
       /// <returns></returns>
        public DBQuote GetQuoteHeaders(string QuoteHeaderID)
        {

            DBQuote Quote = new DBQuote();
            Quote.Header = new DBQuoteHeader();

            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_Quote_header_id = new OracleParameter("p_Quote_header_id", OracleDbType.Int64);
            p_Quote_header_id.Value = QuoteHeaderID;

            OracleParameter p_error = new OracleParameter("p_Error", OracleDbType.Varchar2, ParameterDirection.Output);

            OracleParameter p = new OracleParameter("p_Header",
                                                             OracleDbType.RefCursor,
                                                             ParameterDirection.Output);
            oraParams.Add(p_Quote_header_id);
            oraParams.Add(p);
            oraParams.Add(p_error);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Quote_Header", oraParams, null);

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                Quote.Header.HEADER_ID = ds.Tables[0].Rows[0]["QUOTE_HEADER_ID"].ValueOrEmpty();
                Quote.Header.QUOTE_HEADER_ID = ds.Tables[0].Rows[0]["QUOTE_HEADER_ID"].ValueOrEmpty();
                Quote.Header.QUOTE_NUMBER = ds.Tables[0].Rows[0]["QUOTE_NUMBER"].ValueOrEmpty();
                Quote.Header.DATE_APPROVED = ds.Tables[0].Rows[0]["DATE_APPROVED"].ValueOrEmpty();
                Quote.Header.CONTRACT_NUMBER_MODIFIER = ds.Tables[0].Rows[0]["CONTRACT_NUMBER_MODIFIER"].ValueOrEmpty();
                Quote.Header.LEAD_CONTRACT_NUMBER = ds.Tables[0].Rows[0]["LEAD_CONTRACT_NUMBER"].ValueOrEmpty();
                Quote.Header.SFDC_ACCOUNT_NAME = ds.Tables[0].Rows[0]["SFDC_ACCOUNT_NAME"].ValueOrEmpty();
                Quote.Header.SFDC_ACCOUNT_NUMBER = ds.Tables[0].Rows[0]["SFDC_ACCOUNT_NUMBER"].ValueOrEmpty();
                Quote.Header.INSTALLED_AT_PARTY_ID = ds.Tables[0].Rows[0]["INSTALLED_AT_PARTY_ID"].ValueOrEmpty();
                Quote.Header.INSTALLED_AT_CUSTOMER_NAME = ds.Tables[0].Rows[0]["INSTALLED_AT_CUSTOMER_NAME"].ValueOrEmpty();
                Quote.Header.SOLD_TO_PARTY_ID = ds.Tables[0].Rows[0]["SOLD_TO_PARTY_ID"].ValueOrEmpty();
                Quote.Header.SOLD_TO_CUSTOMER_NAME = ds.Tables[0].Rows[0]["SOLD_TO_CUSTOMER_NAME"].ValueOrEmpty();
                Quote.Header.BILL_TO_CUSTOMER_ID = ds.Tables[0].Rows[0]["BILL_TO_CUSTOMER_ID"].ValueOrEmpty();
                Quote.Header.QUOTE_STATUS = ds.Tables[0].Rows[0]["QUOTE_STATUS"].ValueOrEmpty();
                Quote.Header.OPPORTUNITY_NUMBER = ds.Tables[0].Rows[0]["OPPORTUNITY_NUMBER"].ValueOrEmpty();
                Quote.Header.PRIMARY_QUOTE_FLAG = ds.Tables[0].Rows[0]["PRIMARY_QUOTE_FLAG"].ValueOrEmpty();
                Quote.Header.AUTO_INVOICING_FLAG = ds.Tables[0].Rows[0]["AUTO_INVOICING_FLAG"].ValueOrEmpty();
                Quote.Header.CURRENCY_CODE = ds.Tables[0].Rows[0]["CURRENCY_CODE"].ValueOrEmpty();
                Quote.Header.OPERATING_UNIT_ID = ds.Tables[0].Rows[0]["OPERATING_UNIT_ID"].ValueOrEmpty();
                Quote.Header.OPERATING_UNIT = ds.Tables[0].Rows[0]["OPERATING_UNIT"].ValueOrEmpty();
                Quote.Header.START_DATE = ds.Tables[0].Rows[0]["START_DATE"].ValueOrEmpty();
                Quote.Header.END_DATE = ds.Tables[0].Rows[0]["END_DATE"].ValueOrEmpty();
                Quote.Header.DURATION_UOM = ds.Tables[0].Rows[0]["DURATION_UOM"].ValueOrEmpty();
                Quote.Header.DURATION = ds.Tables[0].Rows[0]["DURATION"].ValueOrEmpty();
                Quote.Header.TOTAL_AMOUNT = ds.Tables[0].Rows[0]["TOTAL_AMOUNT"].ValueOrEmpty();
                Quote.Header.ACV = ds.Tables[0].Rows[0]["ACV"].ValueOrEmpty();
                Quote.Header.ONE_LINE_DISCOUNT = ds.Tables[0].Rows[0]["ONE_LINE_DISCOUNT"].ValueOrEmpty();
                Quote.Header.ONE_LINE_DISCOUNT_TYPE = ds.Tables[0].Rows[0]["ONE_LINE_DISCOUNT_TYPE"].ValueOrEmpty();
                Quote.Header.PRE_PAID_INDICATION = ds.Tables[0].Rows[0]["PRE_PAID_INDICATION"].ValueOrEmpty();
                Quote.Header.MULTI_YEAR_INCENTIVE = ds.Tables[0].Rows[0]["MULTI_YEAR_INCENTIVE"].ValueOrEmpty();
                Quote.Header.CONTRACT_ID = ds.Tables[0].Rows[0]["CONTRACT_ID"].ValueOrEmpty();
                Quote.Header.CREATED_BY = ds.Tables[0].Rows[0]["CREATED_BY"].ValueOrEmpty();
                Quote.Header.CREATION_DATE = ds.Tables[0].Rows[0]["CREATION_DATE"].ValueOrEmpty();
                Quote.Header.LAST_UPDATED_BY = ds.Tables[0].Rows[0]["LAST_UPDATED_BY"].ValueOrEmpty();
                Quote.Header.LAST_UPDATE_DATE = ds.Tables[0].Rows[0]["LAST_UPDATE_DATE"].ValueOrEmpty();
                Quote.Header.LAST_UPDATE_LOGIN = ds.Tables[0].Rows[0]["LAST_UPDATE_LOGIN"].ValueOrEmpty();
                Quote.Header.BILL_TO_SITE_USE_ID = ds.Tables[0].Rows[0]["BILL_TO_SITE_USE_ID"].ValueOrEmpty();
                Quote.Header.BILL_TO_CUSTOMER = ds.Tables[0].Rows[0]["BILL_TO_CUSTOMER_NAME"].ValueOrEmpty();
                Quote.Header.SHIP_TO_CUSTOMER_ID = ds.Tables[0].Rows[0]["SHIP_TO_CUSTOMER_ID"].ValueOrEmpty();
                Quote.Header.SHIP_TO_SITE_USE_ID = ds.Tables[0].Rows[0]["SHIP_TO_SITE_USE_ID"].ValueOrEmpty();
                Quote.Header.SHIP_TO_CUSTOMER = ds.Tables[0].Rows[0]["SHIP_TO_CUSTOMER_NAME"].ValueOrEmpty();
                Quote.Header.NOTES_TO_APPROVERS = ds.Tables[0].Rows[0]["NOTES_TO_APPROVERS"].ValueOrEmpty();
                Quote.Header.CUST_PO_NUMBER = ds.Tables[0].Rows[0]["CUST_PO_NUMBER"].ValueOrEmpty();
                Quote.Header.QUOTE_OWNER = ds.Tables[0].Rows[0]["QUOTE_OWNER"].ValueOrEmpty();
                Quote.Header.BILL_TO_ADDRESS = ds.Tables[0].Rows[0]["BILL_TO_ADDRESS"].ValueOrEmpty();
            }
            return Quote;
        }

        /// <summary>
        /// DELETE
        /// </summary>
        /// <param name="HeaderID"></param>
        /// <returns></returns>
        public string DeleteQuoteHeader(string HeaderID)
        {
            string sRet = string.Empty;

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_header_id = new OracleParameter("p_header_id", OracleDbType.Int64);
            p_header_id.Direction = ParameterDirection.Input;
            p_header_id.Value = HeaderID;

            OracleParameter p_error = new OracleParameter("p_error ", OracleDbType.Varchar2, ParameterDirection.Output);

            oraParams.Add(p_header_id);
            oraParams.Add(p_error);

            sRet = ExecuteQueryReturnParam("NICE_OKS_QUOTING_UTILS.delete_quote_header", oraParams);

            return sRet;
        }



        #endregion

        #region Quote Recent
        
        /// <summary>
        /// Insert
        /// </summary>
        /// <param name="QuoteHeaderID"></param>
        /// <param name="Operation"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public DBQuoteRecent InsertQuoteRecent(string QuoteHeaderID, string Operation, string UserName)
        {
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_header_id = new OracleParameter("p_header_id", OracleDbType.Int64);
            p_header_id.Value = QuoteHeaderID;

            OracleParameter p_user_name = new OracleParameter("p_user_name", OracleDbType.Varchar2);
            p_user_name.Value = UserName;

            OracleParameter p_access_date = new OracleParameter("p_access_date", OracleDbType.Date);
            p_access_date.Value = DateTime.Now;

            OracleParameter p_access_type = new OracleParameter("p_access_type", OracleDbType.Varchar2);
            p_access_type.Value = Operation;


            OracleParameter p_error = new OracleParameter("p_error", OracleDbType.Varchar2);
            p_error.Direction = ParameterDirection.Output;

            oraParams.Add(p_header_id);
            oraParams.Add(p_user_name);
            oraParams.Add(p_access_date);
            oraParams.Add(p_access_type);
            oraParams.Add(p_error);

            DBQuoteRecent newLineItem = ExecuteQueryReturnParamInsertUpdateQuoteRecent("NICE_OKS_QUOTING_UTILS.insert_quote_recent", oraParams);

            return newLineItem;
        }

        /// <summary>
        /// Get
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public List<DBQuoteRecent> GetRecentQuotes(string UserName)
        {
            List<DBQuoteRecent> lstRecentQuotes = new List<DBQuoteRecent>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_user_name = new OracleParameter("p_User_Name", OracleDbType.Varchar2);
            p_user_name.Value = UserName;
            oraParams.Add(p_user_name);

            OracleParameter p_recent = new OracleParameter("p_Recent", OracleDbType.RefCursor);
            p_recent.Direction = ParameterDirection.Output;
            oraParams.Add(p_recent);

            OracleParameter p_error = new OracleParameter("p_Error", OracleDbType.Varchar2);
            p_error.Direction = ParameterDirection.Output;
            oraParams.Add(p_error);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Quote_Recent", oraParams, null);

            try
            {            
                if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        DBQuoteRecent oDbItem = new DBQuoteRecent();

                        oDbItem.QUOTE_HEADER_ID = row["QUOTE_HEADER_ID"].ValueOrEmpty();
                        oDbItem.QUOTE_NUMBER = row["QUOTE_NUMBER"].ValueOrEmpty();
                        oDbItem.CREATION_DATE = row["CREATION_DATE"].ValueOrEmpty();
                        oDbItem.CREATED_BY = row["CREATED_BY"].ValueOrEmpty();
                        oDbItem.QUOTE_STATUS = row["QUOTE_STATUS"].ValueOrEmpty();
                        oDbItem.SOLD_TO_CUSTOMER_NAME = row["SOLD_TO_CUSTOMER_NAME"].ValueOrEmpty();
                        oDbItem.OPPORTUNITY_NUMBER = row["OPPORTUNITY_NUMBER"].ValueOrEmpty();
                        oDbItem.LAST_UPDATE_DATE = row["LAST_UPDATE_DATE"].ValueOrEmpty();
                        oDbItem.USER_NAME = row["LAST_UPDATED_BY"].ValueOrEmpty();
                        //oDbItem.USER_NAME = row["USER_NAME"].ValueOrEmpty();
                        lstRecentQuotes.Add(oDbItem);
                    }
                }
            }
            catch (Exception)
            {              
                throw;
            }
            return lstRecentQuotes;
        }

        #endregion

        #region Customers

        public List<Customer> GetCustomers(string BeginsWith)
        {
            List<Customer> lstCustomers = new List<Customer>();

            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_Begins_With = new OracleParameter("p_Begins_With", OracleDbType.NVarchar2);
            if (string.IsNullOrEmpty(BeginsWith))
            {
                p_Begins_With.Value = DBNull.Value;
            }
            else
            {
                p_Begins_With.Value = BeginsWith;
            }

            oraParams.Add(p_Begins_With);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Customers", oraParams, "p_Customers_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Customer oCust = new Customer();
                    oCust.CustomerID = row["PARTY_ID"].ValueOrEmpty();
                    oCust.CustomerName = row["PARTY_NAME"].ValueOrEmpty();

                    lstCustomers.Add(oCust);
                }
            }
            return lstCustomers;
        }

        #endregion

        #region Accounts


        public List<SFAccount> GetAccounts(string BeginsWith)
        {
            List<SFAccount> lstSFAccount = new List<SFAccount>();

            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_Begins_With = new OracleParameter("p_Begins_With", OracleDbType.NVarchar2);
            if (string.IsNullOrEmpty(BeginsWith))
            {
                p_Begins_With.Value = DBNull.Value;
            }
            else
            {
                p_Begins_With.Value = BeginsWith;
            }

            oraParams.Add(p_Begins_With);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_SF_Accounts", oraParams, "p_SF_Accounts_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    SFAccount oAccount = new SFAccount();
                    oAccount.AccountName = row["account_name"].ValueOrEmpty();
                    oAccount.AccountID = row["account_id"].ValueOrEmpty();
                    oAccount.MasterAccountName = row["master_account_name"].ValueOrEmpty();
                    oAccount.MasterAccountID = row["master_account_id"].ValueOrEmpty();
                    oAccount.MAKey = row["ma_key"].ValueOrEmpty();
                    lstSFAccount.Add(oAccount);
                }
            }
            return lstSFAccount;
        }




        #endregion

        #region Contracts


        public List<DBContract> GetContracts(string PartyID, string MasterAccountID, string CustomFilter, string ExpiratioPeriod, string IncludeWarranty,
       string TopDivision, string TopRegion)
        {
            List<DBContract> lstContracts = new List<DBContract>();

            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_Party_Id = new OracleParameter("p_Party_Id", OracleDbType.Long);
            p_Party_Id.Value = PartyID;

            OracleParameter p_Master_Account_Id = new OracleParameter("p_Master_Account_Id", OracleDbType.NVarchar2);
            if (string.IsNullOrEmpty(MasterAccountID))
            {
                p_Master_Account_Id.Value = DBNull.Value;
            }
            else
            {
                p_Master_Account_Id.Value = MasterAccountID;
            }
            OracleParameter p_Customer_Filter = new OracleParameter("p_Customer_Filter", OracleDbType.NVarchar2);
            if (string.IsNullOrEmpty(CustomFilter))
            {
                p_Customer_Filter.Value = DBNull.Value;
            }
            else
            {
                p_Customer_Filter.Value = CustomFilter;
            }

            OracleParameter p_Expiration_Period = new OracleParameter("p_Expiration_Period", OracleDbType.Int64);
            if (string.IsNullOrEmpty(ExpiratioPeriod))
            {
                p_Expiration_Period.Value = DBNull.Value;
            }
            else
            {
                p_Expiration_Period.Value = ExpiratioPeriod;
            }

            OracleParameter p_Include_Warranty = new OracleParameter("p_Include_Warranty", OracleDbType.NVarchar2);
            if (string.IsNullOrEmpty(ExpiratioPeriod))
            {
                p_Include_Warranty.Value = DBNull.Value;
            }
            else
            {
                p_Include_Warranty.Value = IncludeWarranty;
            }

            OracleParameter p_Top_Division = new OracleParameter("p_Top_Division", OracleDbType.NVarchar2);
            if (string.IsNullOrEmpty(TopDivision))
            {
                p_Top_Division.Value = DBNull.Value;
            }
            else
            {
                p_Top_Division.Value = TopDivision;
            }
              OracleParameter p_Top_Region = new OracleParameter("p_Top_Region", OracleDbType.NVarchar2);
              if (string.IsNullOrEmpty(TopRegion))
            {
                p_Top_Region.Value = DBNull.Value;
            }
            else
            {
                p_Top_Region.Value = TopRegion;
            }


            
            oraParams.Add(p_Party_Id);
            oraParams.Add(p_Master_Account_Id);
            oraParams.Add(p_Customer_Filter);
            oraParams.Add(p_Expiration_Period);
            oraParams.Add(p_Include_Warranty);
            oraParams.Add(p_Top_Division);
            oraParams.Add(p_Top_Region);


            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Contracts", oraParams, "p_Contracts_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBContract oContract = new DBContract();

                    oContract.ID = row["CONTRACT_ID"].ValueOrEmpty();
                    oContract.CONTRACT_NUMBER = row["CONTRACT_NUMBER"].ValueOrEmpty();
                    oContract.CURRENCY_CODE = row["CURRENCY_CODE"].ValueOrEmpty();
                    oContract.SOLD_TO = row["SOLD_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.INSTALLED_AT_CUSTOMER = row["INSTALLED_AT_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_CUSTOMER_NAME = row["BILL_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_COUNTRY = row["BILL_TO_COUNTRY"].ValueOrEmpty();
                    oContract.BILL_TO_CITY = row["BILL_TO_CITY"].ValueOrEmpty();
                    oContract.BILL_TO_SITE_NAME = row["BILL_TO_SITE_NAME"].ValueOrEmpty();
                    oContract.SHIP_TO_COUNTRY = row["SHIP_TO_COUNTRY"].ValueOrEmpty();
                    oContract.SHIP_TO_CITY = row["SHIP_TO_CITY"].ValueOrEmpty();
                    oContract.STATUS = row["STATUS"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    oContract.START_DATE = row["START_DATE"].ValueOrEmpty();
                    oContract.END_DATE = row["END_DATE"].ValueOrEmpty();
                    oContract.ESTIMATED_AMOUNT = row["ESTIMATED_AMOUNT"].ValueOrEmpty();
                    oContract.CONTRACT_TYPE = row["CONTRACT_TYPE"].ValueOrEmpty();
                    oContract.CUSTOMER_CLASS = row["CUSTOMER_CLASS"].ValueOrEmpty();
                    lstContracts.Add(oContract);
                }
            }
            return lstContracts;
        }

        public List<DBContract> GetContractsByNumber(string ContractNumber)
        {
            List<DBContract> lstContracts = new List<DBContract>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Contract_number = new OracleParameter("p_Contract_number", OracleDbType.NVarchar2);

            p_Contract_number.Value = ContractNumber;
            oraParams.Add(p_Contract_number);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Contracts_By_Number", oraParams, "p_Contracts_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBContract oContract = new DBContract();

                    oContract.ID = row["CONTRACT_ID"].ValueOrEmpty();
                    oContract.CONTRACT_NUMBER = row["CONTRACT_NUMBER"].ValueOrEmpty();
                    oContract.CURRENCY_CODE = row["CURRENCY_CODE"].ValueOrEmpty();
                    oContract.SOLD_TO = row["SOLD_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.INSTALLED_AT_CUSTOMER = row["INSTALLED_AT_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_CUSTOMER_NAME = row["BILL_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_COUNTRY = row["BILL_TO_COUNTRY"].ValueOrEmpty();
                    oContract.BILL_TO_CITY = row["BILL_TO_CITY"].ValueOrEmpty();
                    oContract.BILL_TO_SITE_NAME = row["BILL_TO_SITE_NAME"].ValueOrEmpty();
                    oContract.SHIP_TO_COUNTRY = row["SHIP_TO_COUNTRY"].ValueOrEmpty();
                    oContract.SHIP_TO_CITY = row["SHIP_TO_CITY"].ValueOrEmpty();
                    oContract.STATUS = row["STATUS"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    //  oContract.NAME = row["NAME"].ValueOrEmpty();
                    oContract.START_DATE = row["START_DATE"].ValueOrEmpty();
                    oContract.END_DATE = row["END_DATE"].ValueOrEmpty();
                    oContract.ESTIMATED_AMOUNT = row["ESTIMATED_AMOUNT"].ValueOrEmpty();
                    oContract.CONTRACT_TYPE = row["CONTRACT_TYPE"].ValueOrEmpty();
                    oContract.CUSTOMER_CLASS = row["CUSTOMER_CLASS"].ValueOrEmpty();
                    lstContracts.Add(oContract);
                }
            }
            return lstContracts;
        }

        public List<DBContract> GetContractsBySONumber(string SONumber)
        {
            List<DBContract> lstContracts = new List<DBContract>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Order_Number = new OracleParameter("p_Order_Number", OracleDbType.Int64);

            p_Order_Number.Value = SONumber;
            oraParams.Add(p_Order_Number);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Contracts_By_SO_Number", oraParams, "p_Contracts_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBContract oContract = new DBContract();

                    oContract.ID = row["CONTRACT_ID"].ValueOrEmpty();
                    oContract.CONTRACT_NUMBER = row["CONTRACT_NUMBER"].ValueOrEmpty();
                    oContract.CURRENCY_CODE = row["CURRENCY_CODE"].ValueOrEmpty();
                    oContract.SOLD_TO = row["SOLD_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.INSTALLED_AT_CUSTOMER = row["INSTALLED_AT_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_CUSTOMER_NAME = row["BILL_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_COUNTRY = row["BILL_TO_COUNTRY"].ValueOrEmpty();
                    oContract.BILL_TO_CITY = row["BILL_TO_CITY"].ValueOrEmpty();
                    oContract.BILL_TO_SITE_NAME = row["BILL_TO_SITE_NAME"].ValueOrEmpty();
                    oContract.SHIP_TO_COUNTRY = row["SHIP_TO_COUNTRY"].ValueOrEmpty();
                    oContract.SHIP_TO_CITY = row["SHIP_TO_CITY"].ValueOrEmpty();
                    oContract.STATUS = row["STATUS"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    //  oContract.NAME = row["NAME"].ValueOrEmpty();
                    oContract.START_DATE = row["START_DATE"].ValueOrEmpty();
                    oContract.END_DATE = row["END_DATE"].ValueOrEmpty();
                    oContract.ESTIMATED_AMOUNT = row["ESTIMATED_AMOUNT"].ValueOrEmpty();
                    oContract.CONTRACT_TYPE = row["CONTRACT_TYPE"].ValueOrEmpty();
                    oContract.CUSTOMER_CLASS = row["CUSTOMER_CLASS"].ValueOrEmpty();
                    lstContracts.Add(oContract);
                }
            }
            return lstContracts;
        }

        public List<DBContract> GetContractsBySerialNumber(string SerialNumber)
        {
            List<DBContract> lstContracts = new List<DBContract>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_Serial_Number = new OracleParameter("p_Serial_Number", OracleDbType.NVarchar2);

            p_Serial_Number.Value = SerialNumber;
            oraParams.Add(p_Serial_Number);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Contracts_By_Serial_Number", oraParams, "p_Contracts_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBContract oContract = new DBContract();

                    oContract.ID = row["CONTRACT_ID"].ValueOrEmpty();
                    oContract.CONTRACT_NUMBER = row["CONTRACT_NUMBER"].ValueOrEmpty();
                    oContract.CURRENCY_CODE = row["CURRENCY_CODE"].ValueOrEmpty();
                    oContract.SOLD_TO = row["SOLD_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.INSTALLED_AT_CUSTOMER = row["INSTALLED_AT_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_CUSTOMER_NAME = row["BILL_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_COUNTRY = row["BILL_TO_COUNTRY"].ValueOrEmpty();
                    oContract.BILL_TO_CITY = row["BILL_TO_CITY"].ValueOrEmpty();
                    oContract.BILL_TO_SITE_NAME = row["BILL_TO_SITE_NAME"].ValueOrEmpty();
                    oContract.SHIP_TO_COUNTRY = row["SHIP_TO_COUNTRY"].ValueOrEmpty();
                    oContract.SHIP_TO_CITY = row["SHIP_TO_CITY"].ValueOrEmpty();
                    oContract.STATUS = row["STATUS"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    //  oContract.NAME = row["NAME"].ValueOrEmpty();
                    oContract.START_DATE = row["START_DATE"].ValueOrEmpty();
                    oContract.END_DATE = row["END_DATE"].ValueOrEmpty();
                    oContract.ESTIMATED_AMOUNT = row["ESTIMATED_AMOUNT"].ValueOrEmpty();
                    oContract.CONTRACT_TYPE = row["CONTRACT_TYPE"].ValueOrEmpty();
                    oContract.CUSTOMER_CLASS = row["CUSTOMER_CLASS"].ValueOrEmpty();
                    lstContracts.Add(oContract);
                }
            }
            return lstContracts;
        }

        public List<DBContract> GetContractsByPONumber(string PONumber)
        {
            List<DBContract> lstContracts = new List<DBContract>();
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_PO_Number = new OracleParameter("p_PO_Number", OracleDbType.NVarchar2);

            p_PO_Number.Value = PONumber;
            oraParams.Add(p_PO_Number);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.Get_Contracts_By_PO_Number", oraParams, "p_Contracts_Cur");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBContract oContract = new DBContract();

                    oContract.ID = row["CONTRACT_ID"].ValueOrEmpty();
                    oContract.CONTRACT_NUMBER = row["CONTRACT_NUMBER"].ValueOrEmpty();
                    oContract.CURRENCY_CODE = row["CURRENCY_CODE"].ValueOrEmpty();
                    oContract.SOLD_TO = row["SOLD_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.INSTALLED_AT_CUSTOMER = row["INSTALLED_AT_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_CUSTOMER_NAME = row["BILL_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    oContract.BILL_TO_COUNTRY = row["BILL_TO_COUNTRY"].ValueOrEmpty();
                    oContract.BILL_TO_CITY = row["BILL_TO_CITY"].ValueOrEmpty();
                    oContract.BILL_TO_SITE_NAME = row["BILL_TO_SITE_NAME"].ValueOrEmpty();
                    oContract.SHIP_TO_COUNTRY = row["SHIP_TO_COUNTRY"].ValueOrEmpty();
                    oContract.SHIP_TO_CITY = row["SHIP_TO_CITY"].ValueOrEmpty();
                    oContract.STATUS = row["STATUS"].ValueOrEmpty();
                    oContract.OPERATING_UNIT = row["OPERATING_UNIT"].ValueOrEmpty();
                    //  oContract.NAME = row["NAME"].ValueOrEmpty();
                    oContract.START_DATE = row["START_DATE"].ValueOrEmpty();
                    oContract.END_DATE = row["END_DATE"].ValueOrEmpty();
                    oContract.ESTIMATED_AMOUNT = row["ESTIMATED_AMOUNT"].ValueOrEmpty();
                    oContract.CONTRACT_TYPE = row["CONTRACT_TYPE"].ValueOrEmpty();
                    oContract.CUSTOMER_CLASS = row["CUSTOMER_CLASS"].ValueOrEmpty();

                    lstContracts.Add(oContract);
                }
            }
            return lstContracts;
        }


        public string GenerateContract(string headerid, string sUserName)
        {
            List<OracleParameter> oraParams = new List<OracleParameter>();
            string sRet = string.Empty;

            OracleParameter p_header_id = new OracleParameter("p_header_id", OracleDbType.Int64);
            p_header_id.Value = headerid;

            OracleParameter p_User_Name = new OracleParameter("p_User_Name", OracleDbType.Varchar2);
            p_User_Name.Value = sUserName;

            OracleParameter p_error = new OracleParameter("p_error", OracleDbType.Varchar2, ParameterDirection.Output);


            oraParams.Add(p_header_id);
            oraParams.Add(p_User_Name);
            oraParams.Add(p_error);

            sRet = ExecuteQueryReturnParam("NICE_OKS_QUOTING_UTILS.Generate_Contract", oraParams);

            return sRet;
        }
        
        #endregion

        #region Discounts

        /// <summary>
        /// 
        /// </summary>
        /// <param name="HeaderID"></param>
        /// <returns></returns>
        public string RevertDiscounts(string HeaderID)
        {
            string sRet = string.Empty;

            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter p_header_id = new OracleParameter("p_header_id", OracleDbType.Int64);
            p_header_id.Direction = ParameterDirection.Input;
            p_header_id.Value = HeaderID;

            OracleParameter p_error = new OracleParameter("p_error ", OracleDbType.Varchar2, ParameterDirection.Output);

            oraParams.Add(p_header_id);
            oraParams.Add(p_error);

            sRet = ExecuteQueryReturnParam("NICE_OKS_QUOTING_UTILS.Revert_Discounts", oraParams);

            return sRet;
        }

        public string RepriceQuote(string HeaderID, string OneLineDiscountType, string OneLineDiscount, string PrePaidIndication, string MultiYearIncentive)
        {
            string sRet = string.Empty;

            List<OracleParameter> oraParams = new List<OracleParameter>();

            //OracleParameter p_One_Line_Discount_Type = null;
            //OracleParameter p_One_Line_Discount = null;
            //OracleParameter p_Pre_Paid_Indication = null;
            //OracleParameter p_Multi_Year_Incentive = null;

            OracleParameter p_header_id = new OracleParameter("p_header_id", OracleDbType.Int64);
            p_header_id.Direction = ParameterDirection.Input;
            p_header_id.Value = HeaderID;
            oraParams.Add(p_header_id);


                OracleParameter p_One_Line_Discount_Type = new OracleParameter("p_One_Line_Discount_Type", OracleDbType.Varchar2);
                p_One_Line_Discount_Type.Value = OneLineDiscountType.Contains("null") ? null : OneLineDiscountType;
                oraParams.Add(p_One_Line_Discount_Type);

                OracleParameter p_One_Line_Discount = new OracleParameter("p_One_Line_Discount", OracleDbType.Int64);
                p_One_Line_Discount.Value = OneLineDiscount.Contains("null") ? null : OneLineDiscount; 
                oraParams.Add(p_One_Line_Discount);

                if (PrePaidIndication == "Y" && MultiYearIncentive!="null")
                {
                    OracleParameter p_Pre_Paid_Indication = new OracleParameter("p_Pre_Paid_Indication", OracleDbType.Varchar2);
                    p_Pre_Paid_Indication.Value = PrePaidIndication.Contains("null") ? null : PrePaidIndication;
                    oraParams.Add(p_Pre_Paid_Indication);


                    OracleParameter p_Multi_Year_Incentive = new OracleParameter("p_Multi_Year_Incentive", OracleDbType.Int64);
                    p_Multi_Year_Incentive.Value = MultiYearIncentive.Contains("null") ? null : MultiYearIncentive;
                    oraParams.Add(p_Multi_Year_Incentive);
                }
                else
                {
                    OracleParameter p_Pre_Paid_Indication = new OracleParameter("p_Pre_Paid_Indication", OracleDbType.Varchar2);
                    p_Pre_Paid_Indication.Value = null;
                    oraParams.Add(p_Pre_Paid_Indication);


                    OracleParameter p_Multi_Year_Incentive = new OracleParameter("p_Multi_Year_Incentive", OracleDbType.Int64);
                    p_Multi_Year_Incentive.Value = null;
                    oraParams.Add(p_Multi_Year_Incentive);
                }



            OracleParameter p_error = new OracleParameter("p_error ", OracleDbType.Varchar2, ParameterDirection.Output);                                
            oraParams.Add(p_error);

            sRet = ExecuteQueryReturnParam("NICE_OKS_QUOTING_UTILS.Reprice_Quote", oraParams);

            return sRet;
        }


        //RevertDiscounts
        #endregion

        #region People

        /// <summary>
        /// 
        /// </summary>
        /// <param name="QuoteNumber"></param>
        /// <param name="FromDate"></param> 
        /// <param name="ToDate"></param>
        /// <param name="CreatedBy"></param>
        /// <param name="UpdatedBy"></param>
        /// <param name="SFDCAccountID"></param>
        /// <param name="SoldToID"></param>
        /// <param name="Contract"></param>
        /// <returns></returns>
        public List<DBQuote> FindQuotes(string QuoteNumber, string FromDate, string ToDate,
                                                    string CreatedBy, string UpdatedBy, string SFDCAccountID,
                                                       string SoldToID, string Contract)
        {
            List<DBQuote> lstQuotes = new List<DBQuote>();

            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_quote_number = new OracleParameter("p_quote_number", OracleDbType.Varchar2);
            p_quote_number.Value = QuoteNumber;

            DateTime dtFrom;
            OracleParameter p_from_date = new OracleParameter("p_from_date", OracleDbType.Date);

            if (DateTime.TryParse(FromDate, out dtFrom))
            {
                p_from_date.Value = dtFrom;
            }
            DateTime dtTO;
            DateTime.TryParse(ToDate, out dtTO);

            OracleParameter p_to_date = new OracleParameter("p_to_date", OracleDbType.Date);
            if (DateTime.TryParse(ToDate, out dtTO))
            {
                p_to_date.Value = dtTO;
            }

            OracleParameter p_created_by = new OracleParameter("p_created_by", OracleDbType.Varchar2);
            p_created_by.Value = CreatedBy;

            OracleParameter p_updated_by = new OracleParameter("p_updated_by", OracleDbType.Varchar2);
            p_updated_by.Value = UpdatedBy;

            OracleParameter p_sfdc_account_id = new OracleParameter("p_sfdc_account_id", OracleDbType.Varchar2);
            p_sfdc_account_id.Value = SFDCAccountID;

            OracleParameter p_sold_to_id = new OracleParameter("p_sold_to_id", OracleDbType.Varchar2);
            p_sold_to_id.Value = SoldToID;

            OracleParameter p_Contract_Number = new OracleParameter("p_Contract_Number", OracleDbType.Varchar2);
            p_Contract_Number.Value = Contract;

            oraParams.Add(p_quote_number);
            oraParams.Add(p_from_date);
            oraParams.Add(p_to_date);
            oraParams.Add(p_created_by);
            oraParams.Add(p_updated_by);
            oraParams.Add(p_sfdc_account_id);
            oraParams.Add(p_sold_to_id);
            oraParams.Add(p_Contract_Number);

            DataSet ds = ExecuteOraProcedure("NICE_OKS_QUOTING_UTILS.find_quote", oraParams, "p_quote_result");

            if (ds != null && ds.Tables != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DBQuote quote = new DBQuote();
                    quote.Header = new DBQuoteHeader();
                    quote.Header.QUOTE_HEADER_ID = row["QUOTE_HEADER_ID"].ValueOrEmpty();
                    quote.Header.QUOTE_NUMBER = row["QUOTE_NUMBER"].ValueOrEmpty();
                    quote.Header.CREATION_DATE = row["CREATION_DATE"].ValueOrEmpty();
                    quote.Header.CREATED_BY = row["CREATED_BY"].ValueOrEmpty();
                    quote.Header.QUOTE_STATUS = row["QUOTE_STATUS"].ValueOrEmpty();
                    quote.Header.INSTALLED_AT_CUSTOMER_NAME = row["SOLD_TO_CUSTOMER_NAME"].ValueOrEmpty();
                    quote.Header.OPPORTUNITY_NUMBER = row["OPPORTUNITY_NUMBER"].ValueOrEmpty();
                    quote.Header.LAST_UPDATE_DATE = row["LAST_UPDATE_DATE"].ValueOrEmpty();
                    quote.Header.LAST_UPDATED_BY = row["LAST_UPDATED_BY"].ValueOrEmpty();
                    lstQuotes.Add(quote);
                }
            }
            return lstQuotes;
        }

        #endregion

        #region WorkFlows

        public string StartProcess(string quotenumber)
        {
            string sRet = string.Empty;

            List<OracleParameter> oraParams = new List<OracleParameter>();
            OracleParameter p_Quote_header_id = new OracleParameter("p_Quote_header_id", OracleDbType.Int64);
            p_Quote_header_id.Value = quotenumber;
            oraParams.Add(p_Quote_header_id);

            sRet = ExecuteQueryReturnParam("NICE_OKS_QUOTE_WF.start_process", oraParams);

            return sRet;
        }

        #endregion
        #region DAL

        /// <summary>
        /// 
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameters"></param>
        /// <param name="outRefCursorName"></param>
        /// <returns></returns>
        private DataSet ExecuteOraProcedure(string procName, List<OracleParameter> parameters, string outRefCursorName)
        {
            DataSet dtResult = new DataSet();
            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    if (!string.IsNullOrEmpty(outRefCursorName))
                    {
                        OracleParameter p = new OracleParameter(outRefCursorName,
                                                                OracleDbType.RefCursor,
                                                                ParameterDirection.Output);
                        cmd.Parameters.Add(p);
                    }

                    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                    {
                        da.Fill(dtResult);
                    }
                }

                return dtResult;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private DBQuote ExecuteOraProcedureMultipleCursor(string procName, List<OracleParameter> parameters)
        {
            DBQuote oInitialQuote = new DBQuote();
            DataSet dtResult = new DataSet();
            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                    {
                        da.Fill(dtResult);

                        oInitialQuote.Header.START_DATE = cmd.Parameters["p_Quote_Start_Date"].Value.ValueOrEmpty();
                        oInitialQuote.Header.END_DATE = cmd.Parameters["p_Quote_End_Date"].Value.ValueOrEmpty();
                        oInitialQuote.Header.DURATION = cmd.Parameters["p_Duration"].Value.ValueOrEmpty();
                        oInitialQuote.Header.DURATION_UOM = cmd.Parameters["p_duration_uom"].Value.ValueOrEmpty();
                        oInitialQuote.Header.TOTAL_AMOUNT = cmd.Parameters["p_Total_Amount"].Value.ValueOrEmpty();

                        if (dtResult != null && dtResult.Tables != null && dtResult.Tables[0] != null && dtResult.Tables[0].Rows.Count > 0)
                        {
                            oInitialQuote.SubLines = new List<DBSubLineItem>();

                            foreach (DataRow row in dtResult.Tables[0].Rows)
                            {

                                DBSubLineItem _subLine = new DBSubLineItem();

                                _subLine.MAINTENANCE_ITEM = row["MAINTENANCE_ITEM"].ValueOrEmpty();
                                _subLine.COVERED_PRODUCT = row["COVERED_PRODUCT"].ValueOrEmpty();
                                _subLine.SOLUTION_VERSION = row["SOLUTION_VERSION"].ValueOrEmpty();
                                _subLine.SUNSET_PRODUCT = row["SUNSET_PRODUCT"].ValueOrEmpty();
                                _subLine.INSTANCE_NUMBER = row["INSTANCE_NUMBER"].ValueOrEmpty();
                                _subLine.SERIAL_NUMBER = row["SERIAL_NUMBER"].ValueOrEmpty();
                                _subLine.DIVISION_NAME = row["DIVISION_NAME"].ValueOrEmpty();
                                _subLine.QUANTITY = row["QUANTITY"].ValueOrEmpty();
                                _subLine.NUMBER_LICENCES = row["NUMBER_LICENCES"].ValueOrEmpty();
                                _subLine.START_DATE = row["START_DATE"].ValueOrEmpty();
                                _subLine.END_DATE = row["END_DATE"].ValueOrEmpty();
                                _subLine.DURATION = row["DURATION"].ValueOrEmpty();
                                _subLine.SALES_ORDER_NUMBER = row["SALES_ORDER_NUMBER"].ValueOrEmpty();
                                _subLine.PRODUCT_INITIAL_PO_NUMBER = row["PRODUCT_INITIAL_PO_NUMBER"].ValueOrEmpty();
                                _subLine.SOURCE_CONTRACT_PO_NUM = row["LAST_RENEWAL_CONTRACT_PO_NUM"].ValueOrEmpty();
                                _subLine.PRODUCT_ORIG_SHIP_DATE = row["PRODUCT_ORIG_SHIP_DATE"].ValueOrEmpty();
                                _subLine.CONTRACT_IDENTIFIER = row["CONTRACT_IDENTIFIER"].ValueOrEmpty();
                                _subLine.SUBLINE_STATUS = row["SUBLINE_STATUS"].ValueOrEmpty();
                                _subLine.TOTAL_LIST_PRICE = row["TOTAL_LIST_PRICE"].ValueOrEmpty();
                                _subLine.TOTAL_NET_PRICE = row["TOTAL_NET_PRICE"].ValueOrEmpty();
                                _subLine.PREVIOUS_AMOUNT = row["PREVIOUS_AMOUNT"].ValueOrEmpty();
                                _subLine.MAINTENANCE_RATE = row["MAINTENANCE_RATE"].ValueOrEmpty();
                                _subLine.CURRENT_AMOUNT = row["CURRENT_AMOUNT"].ValueOrEmpty();
                                _subLine.COL = row["COL"].ValueOrEmpty();
                                _subLine.SOURCE_ORDER = row["SOURCE_ORDER"].ValueOrEmpty();
                                _subLine.SOURCE_CONTRACT = row["SOURCE_CONTRACT"].ValueOrEmpty();

                                oInitialQuote.SubLines.Add(_subLine);
                            }
                        }

                        if (dtResult != null && dtResult.Tables != null && dtResult.Tables[1] != null && dtResult.Tables[1].Rows.Count > 0)
                        {
                            oInitialQuote.Lines = new List<DBLineItem>();

                            foreach (DataRow row in dtResult.Tables[1].Rows)
                            {
                                DBLineItem _Line = new DBLineItem();

                                _Line.MAINTENANCE_ITEM = row["MAINTENANCE_ITEM"].ValueOrEmpty();
                                _Line.MAINTENANCE_ITEM_DESCRIPTION = row["MAINTENANCE_ITEM_DESCRIPTION"].ValueOrEmpty();
                                _Line.MAINTENANCE_POLICY = row["MAINTENANCE_POLICY"].ValueOrEmpty();
                                _Line.MAINTENANCE_POLICY_SOURCE = row["MAINTENANCE_POLICY_SOURCE"].ValueOrEmpty();
                                _Line.MAINTENANCE_TYPE = row["MAINTENANCE_TYPE"].ValueOrEmpty();
                                _Line.TOTAL_AMOUNT = row["TOTAL_AMOUNT"].ValueOrEmpty();
                                //    _Line.COL = row["COL_PCT"].ValueOrEmpty();

                                oInitialQuote.Lines.Add(_Line);
                            }
                        }
                    }
                }
                return oInitialQuote;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private string ExecuteQueryReturnParam(string procName, List<OracleParameter> parameters)
        {
            string sRet = string.Empty;

            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    if (cmd.Parameters["p_error"]!=null)
                    {
                        sRet = cmd.Parameters["p_error"].Value.ValueOrEmpty();                 
                    }
                    
                }
                return sRet;
            }
        }
        private DBQuote ExecuteQueryReturnParamUpdateSubLineItem(string procName, List<OracleParameter> parameters)
        {
            DBQuote newDbItem = new DBQuote();

            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();

                    newDbItem.ERROR = cmd.Parameters["p_error"].Value.ValueOrEmpty();
                }

                return newDbItem;
            }
        }

        private DBQuote ExecuteQueryCopy(string procName, List<OracleParameter> parameters)
        {
            DBQuote quote = new DBQuote();
            quote.Header = new DBQuoteHeader();

            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    quote.Header.HEADER_ID = cmd.Parameters["p_New_Header_Id"].Value.ValueOrEmpty();
                    quote.ERROR = cmd.Parameters["p_error"].Value.ValueOrEmpty();
                }
                return quote;
            }
        }

        private DBQuote ExecuteQueryReturnParamUpdateQueryHeader(string procName, List<OracleParameter> parameters)
        {
            DBQuote quote = new DBQuote();
            quote.Header = new DBQuoteHeader();

            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();

                    quote.ERROR = cmd.Parameters["p_error"].Value.ValueOrEmpty();
                }
                return quote;
            }
        }

        private DBLineItem ExecuteQueryReturnParamInsertUpdateLineItem(string procName, List<OracleParameter> parameters)
        {
            DBLineItem newLineItem = new DBLineItem();

            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();

                    newLineItem.ERROR = cmd.Parameters["p_error"].Value.ValueOrEmpty();
                }
                return newLineItem;
            }
        }


        private DBQuote ExecuteQueryReturnParamCreateQuote(string procName, List<OracleParameter> parameters)
        {
            DBQuote newQuote = new DBQuote();
            newQuote.Header = new DBQuoteHeader();

            
            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();

                    if (cmd.Parameters.Count > 1)
                    {
                        newQuote.Header.HEADER_ID = cmd.Parameters["p_quote_header_id"].Value.ValueOrEmpty();
                    }
                    newQuote.ERROR = cmd.Parameters["p_error_msg"].Value.ValueOrEmpty();
                }
                return newQuote;
            }
        }

        private DBQuoteRecent ExecuteQueryReturnParamInsertUpdateQuoteRecent(string procName, List<OracleParameter> parameters)
        {
            DBQuoteRecent newLineItem = new DBQuoteRecent();

            using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORA"].ConnectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;

                    if (parameters != null &&
                        parameters.Any())
                    {
                        foreach (OracleParameter prm in parameters)
                        {
                            cmd.Parameters.Add(prm);
                        }
                    }

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();

                    newLineItem.ERROR = cmd.Parameters["p_error"].Value.ValueOrEmpty();
                }
                return newLineItem;
            }
        }
        #endregion




    }
}